如何计算列第1行+第2行的值 [英] How to calculate value of column row 1 + row 2

查看:112
本文介绍了如何计算列第1行+第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屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆