在SQL查询中计算 [英] Computing in SQL Queries

问题描述

嗨！



我想计算逻辑在哪里这样：



表A有一个名为TotalProductSum的列

Hi!

I want to compute in where the logic is somehow like this:

Table A has a column named "TotalProductSum"

ID||TotalProductSum||
11||1000.00        ||

表B有一个名为ProductSum的栏目

Table B has a column named "ProductSum"

ID||Products||Value||
11||01      ||20   ||
11||02      ||35   ||
11||03      ||40   ||
11||04      ||10   ||

* ProductSum在每个单元格中都有.00，这应该计算为TotalProductSum - ProductSum。一旦将TotalProductSum减去产品总和，它就会更改。例如，TotalProductSum = 100，ProductSum = 10，则NewTotalProductSum = 90. ProductSum的下一个值= 30，然后计算将更改为NewTotalProductSum - ProductSum，然后下一个单元格的NewTotalProductSum为60.



输出表：

TotalProductSum = 1000.00

*ProductSum has .00 in each cell and this should be computed as TotalProductSum - ProductSum. The TotalProductSum changes once it is subtracted to product sum. For example TotalProductSum = 100 and ProductSum = 10 then the NewTotalProductSum = 90. The next value of ProductSum = 30 then the computation will change to NewTotalProductSum - ProductSum then the NewTotalProductSum of the next cell is 60.

OUTPUT TABLE:
TotalProductSum = 1000.00

ID||Products||Value||NewTotalProductSum [Value - [Previous]TotalProductSum]
11||01      ||20   ||800.00
11||02      ||35   ||765.00
11||03      ||40   ||725.00
11||04      ||10   ||715.00

TotalProductSum是Value和Previous TotalProductSum的差值。



如果您没有得到问题，请随时在下面发表评论。谢谢。

TotalProductSum is the difference of Value and the Previous TotalProductSum.

If you do not get the question, feel free to comment below. Thank you.

推荐答案

我为你做了样品检查希望这对你有帮助。

I have made sample for you check this hope this will help you.

-- Create Table 
CREATE TABLE [dbo].[TableA](
	[ID] [INT] NOT NULL,
	[TotalProductSum] [INT] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[TableB](
	[ID] [INT] NOT NULL,
	[Products] [varchar](30) NOT NULL,
	[Value] [INT] NOT NULL
) ON [PRIMARY]


-- Insert Sample Data
INSERT INTO [dbo].[TableA]
           ([ID],[TotalProductSum])
     VALUES (11,1000)
           
           INSERT INTO [TestTB].[dbo].[TableB] ([ID],[Products],[Value])
     VALUES (11 ,'10' ,80)

    INSERT INTO [TestTB].[dbo].[TableB] ([ID],[Products],[Value])
     VALUES (11 ,'20' ,30)
      INSERT INTO [TestTB].[dbo].[TableB] ([ID],[Products],[Value])
     VALUES (11 ,'30' ,45)
      INSERT INTO [TestTB].[dbo].[TableB] ([ID],[Products],[Value])
     VALUES (11 ,'40' ,56)

-- Select query 

select * from TableA
select * from TableB

-- Here isw the select query with your result : 


SELECT A.ID,A.Products,A.Value,B.TotalproductSum- SUM(a.value) OVER (ORDER BY A.Products) NewTotalProductSum 
from  
TableB as A INNER JOIN TableA AS B
ON A.ID=B.ID
ORDER BY a.id;

结果将是这样的

表A

11 1000

TableB

11 10 80

11 20 30

11 30 45

11 40 56



结果查询将是这样的（

The Result will be like this
Table A
11 1000
TableB
11 10 80
11 20 30
11 30 45
11 40 56

The result Query will be like this (

NewTotalProductSum [Value - [Previous]TotalProductSum]

）

11 10 80 920

11 20 30 890

11 30 45 845

11 40 56 789

)
11 10 80 920
11 20 30 890
11 30 45 845
11 40 56 789

这篇关于在SQL查询中计算的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！