如何计算列第1行+第2行的值 [英] How to calculate value of column row 1 + row 2
问题描述
我有这样的数据
< pre> ClientID JumlahHadir月份
20001712 1 4 2015
20001712 2 5 2015
20001712 2 6 2015
20001712 2 7 2014
20001712 1 8 2014
我想要的数据如同这个
<前lang =HTML> ClientID JumlahHadir SumTotal Bulan Tahun
20001712 1 1 4 2015
20001712 2 3 5 2015
20001712 2 4 6 2015
20001712 2 4 7 2014
20001712 1 3 8 2014
So SumTotal1 = JumlahHadir1
SumTotal2 = JumlahHadir1 + JumlahHadir2
SumTotal3 = JumlahHadir2 + JumlahHadir3
dst
如何在sql server中查询这些数据?很快就需要你的帮助。使SumTotal的值= JumlahHadir第1行+ umlahHadir第2行
我尝试过:
我试过这样的脚本
SELECT t.clientID,t.JumlahClient,t.Bulan,t.Tahun,tt.TotalBonus
FROM GroupAttendance t外部申请
(SELECT Sum(t.JumlahClient + t.JumlahClient)为TotalBonus
来自GroupAttendance t2
WHERE t2.clientID = t .clientID
group by bulan,tahun
)tt;
感谢您的帮助。
问候,
lowis
如果如果您使用的是SQL 2012或更高版本,则可以使用LAG访问上一行数据: https://msdn.microsoft.com/en-GB/library/hh231256.aspx?f=255&MSPPError=-2147217396 [ ^ ]
这样的东西,但是这个将比使用LAG方法慢:
WITH CTE AS(
SELECT rownum = ROW_NUMBER()OVER(ORDER BY a.clientID),a.JumlahClient
FROM GroupAttendance a
)
SELECT
CTE.JumlahClient JumlahHadir,
prev.JumlahClient + CTE.JumlahClient SumTotal
来自CTE
LEFT JOIN CTE prev ON prev.rownum = CTE.rownum - 1
I have data like this
<pre>ClientID JumlahHadir Months Years
20001712 1 4 2015
20001712 2 5 2015
20001712 2 6 2015
20001712 2 7 2014
20001712 1 8 2014
I want the data like this
ClientID JumlahHadir SumTotal Bulan Tahun
20001712 1 1 4 2015
20001712 2 3 5 2015
20001712 2 4 6 2015
20001712 2 4 7 2014
20001712 1 3 8 2014
So SumTotal1 = JumlahHadir1
SumTotal2 = JumlahHadir1 + JumlahHadir2
SumTotal3 = JumlahHadir2 + JumlahHadir3
dst
How to query this data in sql server? Needs your help soon guys. Make the value of SumTotal = JumlahHadir row 1 + umlahHadir row 2
What I have tried:
I have tried script like this
SELECT t.clientID, t.JumlahClient, t.Bulan, t.Tahun, tt.TotalBonus
FROM GroupAttendance t outer apply
(SELECT Sum(t.JumlahClient+ t.JumlahClient) as TotalBonus
FROM GroupAttendance t2
WHERE t2.clientID = t.clientID
group by bulan, tahun
) tt;
Thank you for helping.
Regards,
lowis
If you are using SQL 2012 or higher, then you can use LAG to access the previous row data: https://msdn.microsoft.com/en-GB/library/hh231256.aspx?f=255&MSPPError=-2147217396[^]
Something like this, but this will be slower than using the LAG method:
WITH CTE AS( SELECT rownum = ROW_NUMBER() OVER (ORDER BY a.clientID), a.JumlahClient FROM GroupAttendance a ) SELECT CTE.JumlahClient JumlahHadir, prev.JumlahClient + CTE.JumlahClient SumTotal FROM CTE LEFT JOIN CTE prev ON prev.rownum = CTE.rownum - 1
这篇关于如何计算列第1行+第2行的值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!