SQLSERVER中使用SELECT的计算列和累计列 [英] Computed and Cummulative Columns With SELECT in SQLSERVER
问题描述
你好，
我有一个来自3个表的SELECT子句的结果给了我这些列（我使用了UNION ALL和ORDER BY日期，每个表都有一个Amount，它们有共同点日期栏）：
（身份证，日期，金额_1，金额_2，金额_3）
我现在需要选择两个新的列（第6和第7）将按如下方式计算：
Amount_4 = Amount1-（Amount_2 + Amount3）
和Amount_5（当前行的）= Amount_5（上一行） - Amount_4（当前一行）
所以我终于得到了这个：
（身份证，日期，金额_1，金额_2，金额_3，金额_4，金额_5）
感谢您的帮助
如果您需要更多解释请注释：）
Hello,
I have a result of a SELECT clause from 3 Tables that gives me these columns ( I used UNION ALL and ORDER BY date each table has an Amount , and they have in common the Date column) :
(ID,Date,Amount_1,Amount_2,Amount_3)
NOW I need to Select two new Columns (6th and 7th) that will computed like this :
Amount_4 = Amount1-(Amount_2 + Amount3)
And Amount_5 (Of the Current Row) = Amount_5 (of the previous Row) - Amount_4 (Of the Current Row )
So I get finally this :
(ID,Date,Amount_1,Amount_2,Amount_3,Amount_4, Amount_5)
Thanks for Helping me
If you need more explanation please comment : )
推荐答案
给出如下表：
Given a table like this:
CREATE TABLE [cpqa].[tbl_TT_dataSignal](
[ID][int],
[Date][datetime],
[Amount_01][int],
[Amount_02][int],
[Amount_03][int]
)
添加一些数据。认识到这个解决方案是你想要的：
Add some data. Realze that this solution is "like" whnt you want:
INSERT INTO [cpqa].[tbl_TT_dataSignal]
VALUES(1,'2013-09-22 10:50:58.400',5141000972,128,256),
(2,'2013-02-05 09:55:53.857',2105058535,16,32),
(3,'2013-04-17 14:05:39.663',1317579732,512,2048),
(4,'2013-02-11 10:33:59.810',213575799,6144,7936)
查询'SELECT * FROM [cpqa]。[tbl_TT_dataSignal]'，获得：
Querying 'SELECT * FROM [cpqa].[tbl_TT_dataSignal]', one obtains:
ID Date Amount_01 Amount_02 Amount_03
1 2013-09-22 10:50:58.400 5141000972 128 256
2 2013-02-05 09:55:53.857 2105058535 16 32
3 2013-04-17 14:05:39.663 1317579732 512 2048
4 2013-02-11 10:33:59.810 213575799 6144 7936
（此处质量保证格式中没有格式），现在尝试计算：
(format here in QA not withstanding), now try a calculation:
SELECT [ID], [Date],[Amount_01], [Amount_02], [Amount_03], [Amount_01]-([Amount_02]+[Amount_03]) AS[Amount_04]/*, [Amount_04] + [Amount_05] AS [Amount_05]*/ FROM [cpqa].[tbl_TT_dataSignal]
因此会放弃一张桌子：
That'll give up a table thus:
ID Date Amount_01 Amount_02 Amount_03 Amount_04
1 2013-09-22 10:50:58.400 5141000972 128 256 5141000588
2 2013-02-05 09:55:53.857 2105058535 16 32 2105058487
3 2013-04-17 14:05:39.663 1317579732 512 2048 1317577172
4 2013-02-11 10:33:59.810 213575799 6144 7936 213561719
通过创建另一个表来加长步伐。只是说：
Lengthen stride some by creating another table. Just saying:
CREATE TABLE [cpqa].[tbl_TT_dataSignalTwo](
[ID][int],
[Date][datetime],
[Amount_01][bigint],
[Amount_02][bigint],
[Amount_03][bigint],
[Amount_04][bigint]
)
INSERT INTO [cpqa].[tbl_TT_dataSignalTwo]
SELECT [ID], [Date],[Amount_01], [Amount_02], [Amount_03], [Amount_01]-([Amount_02]+[Amount_03]) AS[Amount_04] FROM [cpqa].[tbl_TT_dataSignal]
SELECT [ID], [Date],[Amount_01], [Amount_02], [Amount_03], [Amount_04] FROM [cpqa].[tbl_TT_dataSignalTwo]
此查询'SELECT [Amount_04] FROM [cpqa]。[tbl_TT_dataSignalTwo]
'
获取：
And this query 'SELECT [Amount_04] FROM [cpqa].[tbl_TT_dataSignalTwo]
'
Gets:
Amount_04
~~~~~~~~~~
5141000588
2105058487
1317577172
213561719
最后：
Then finally:
SELECT [ID], [Date],[Amount_01], [Amount_02], [Amount_03], [Amount_04], [Amount_04] + COALESCE((SELECT SUM([Amount_04]) FROM [cpqa].[tbl_TT_dataSignalTwo] b WHERE b.ID<a.ID),0) AS [Amount_05] FROM [cpqa].[tbl_TT_dataSignalTwo] a
获取：
Gets:
ID Date Amount_01 Amount_02 Amount_03 Amount_04 Amount_05
1 2013-09-22 10:50:58.400 5141000972 128 256 5141000588 5141000588
2 2013-02-05 09:55:53.857 2105058535 16 32 2105058487 7246059075
3 2013-04-17 14:05:39.663 1317579732 512 2048 1317577172 8563636247
4 2013-02-11 10:33:59.810 213575799 6144 7936 213561719 8777197966
如果数据逆行或不符合规范（最后一列总计运行），最终输出可能需要ORDER BY子句
That final output might require an ORDER BY clause if the data appears in retrograde or otherwise not to spec (last column running total)
这篇关于SQLSERVER中使用SELECT的计算列和累计列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
