sql查询自联接以查找日期差异的总和 [英] sql query self join to find sum of difference in date

问题描述

我有一张表Absent

 Id sid Startdate enddate Reason 
 id 
 1 1 1/1/2013 3/1/2013 1 
 2 1 4 / 1/2013 5/1/2013 2 
 3 2 12/12/2012 20/12/2012 1 
 4 2 5/1/2013 7/1/2013 3 
 5 1 2013年8月1日9/1/2013 1 

我想查找学生因某种原因缺席的总天数.ie找出原因的天差总和。看起来好像

 Sid Reason1 Reason2 Reason3 
 1 3 1 0 
 2 8 0 2 

解决方案

为了帮助您开始计算两个日期之间的差异，请查看日期 [ ^ ]。这有助于您计算开始日期和结束日期之间的差异。



现在完成后，您可以使用PIVOT将结果修改为所需的格式。有关数据透视的信息，请参阅使用PIVOT和UNPIVOT [ ^ ]

Pivot查询

使用下面的查询来获得结果

 选择 sid，isnull（sum（[ 1 ]）， 0 ） as  Reason1，isnull（sum（[ 2 ]）， 0 ） as  Reason2，isnull（sum（[ 3 ]）， 0 ） as  Reason3 来自 
（
 < span class =code-keyword> SELECT  sid，[ 1 ]，[ 2  ]，[ 3 ] 
  FROM  
（
 选择 id，sid， datediff（d，Startdate，enddate） as  diff，Reason  from   TBLNM  
）up 
 PIVOT（SUM（差异） FOR 原因 IN （[ 1 ]，[ 2 ]，[ 3 ] ）） AS  pvt 
）
  as  t 组 按 sid 

快乐编码！

：）

I have a table Absent

Id	sid	Startdate	enddate	     Reason
id
1	1	1/1/2013	3/1/2013	1
2	1	4/1/2013	5/1/2013	2
3	2	12/12/2012	20/12/2012	1
4	2	5/1/2013	7/1/2013	3
5	1	8/1/2013	9/1/2013	1

I want to find total number of days that a student absent for a reason .i.e. find sum of difference of days for a reason.Out put look like

Sid	Reason1	Reason2	Reason3		
1	3	1	0		
2	8	0	2		

解决方案

To get you started, to calculate the difference between two dates, have a look at DATEDIFF[^]. This helps you to calculate the difference between the start and the end date.

Now when that is done, you can use PIVOT to modify the result to desired format. For information about pivot, see Using PIVOT and UNPIVOT[^]

Pivot Query
use below query to get result

select sid, isnull(sum([1]),0) as Reason1,isnull(sum([2]),0) as Reason2,isnull(sum([3]),0) as Reason3 from
(
    SELECT sid, [1], [2] , [3]
    FROM
    (
        select id,sid,datediff(d,Startdate,enddate) as diff,Reason from TBLNM
    ) up
    PIVOT (SUM(diff) FOR Reason IN ([1], [2],[3])) AS pvt
)
as t group by sid

Happy Coding!
:)

这篇关于sql查询自联接以查找日期差异的总和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！