CREATE TABLE TEST(A INT,B INT) INSERT TEST SELECT 3,9 INSERT TEST SELECT 2,19 SELECT * FROM TESTSELECT SUM(CASE WHEN B-A<=5 THEN 1 ELSE 0 END) AS [0-5], SUM(CASE WHEN B-A>5 AND B-A<=10 THEN 1 ELSE 0 END) AS [5-10], SUM(CASE WHEN B-A>10 AND B-A<=20 THEN 1 ELSE 0 END) AS [10-20], SUM(CASE WHEN B-A>20 AND B-A<=50 THEN 1 ELSE 0 END) AS [20-50], SUM(CASE WHEN B-A>50 AND B-A<=60 THEN 1 ELSE 0 END) AS [50-60] FROM TESTDROP TABLE TEST
select count(*) from tablename where datediff(s,a,b)>=0 and datediff(s,a,b)<=5
--try declare @t table (a int ,b int ) insert into @t select 1,2 union all select 1,4 union all select 1,3 union all select 1,8 union all select 2,44 union all select 1,22 union all select 3,3 union all select 3,5 union all select 1,4 union all select 7,8 union all select 5,50 select sum(case when b-a>=0 and b-a<5 then 1 else 0 end ) as [0-5] ,sum(case when b-a>=5 and b-a<10 then 1 else 0 end ) as [5-10] ,sum(case when b-a>=10 and b-a<20 then 1 else 0 end ) as [10-20] ,sum(case when b-a>=20 and b-a<50 then 1 else 0 end ) as [20-50] ,sum(case when b-a>=50 and b-a<60 then 1 else 0 end ) as [50-60] from @t --结果0-5 5-10 10-20 20-50 50-60 ----------- ----------- ----------- ----------- ----------- 7 1 0 3 0(所影响的行数为 1 行)
select sum(case when datediff(second, a, b) > 0 and datediff(second, a, b) <= 5 then 1 else 0 end) as '0~5', sum(case when datediff(second, a, b) > 5 and datediff(second, a, b) <= 10 then 1 else 0 end) as '5~10', sum(case when datediff(second, a, b) > 10 and datediff(second, a, b) <= 20 then 1 else 0 end) as '10~20', sum(case when datediff(second, a, b) > 20 and datediff(second, a, b) <= 50 then 1 else 0 end) as '20~50', sum(case when datediff(second, a, b) > 50 and datediff(second, a, b) <= 60 then 1 else 0 end) as '50~60' from Table
SELECT SUM(CASE WHEN DATEDIFF(s,A,B)<=5 THEN 1 ELSE 0 END) AS [0-5], SUM(CASE WHEN DATEDIFF(s,A,B)>5 AND DATEDIFF(s,A,B)<=10 THEN 1 ELSE 0 END) AS [5-10], SUM(CASE WHEN DATEDIFF(s,A,B)>10 AND DATEDIFF(s,A,B)<=20 THEN 1 ELSE 0 END) AS [10-20], SUM(CASE WHEN DATEDIFF(s,A,B)>20 AND DATEDIFF(s,A,B)<=50 THEN 1 ELSE 0 END) AS [20-50], SUM(CASE WHEN DATEDIFF(s,A,B)>50 AND DATEDIFF(s,A,B)<=60 THEN 1 ELSE 0 END) AS [50-60] FROM TEST
INSERT TEST SELECT 3,9
INSERT TEST SELECT 2,19
SELECT * FROM TESTSELECT
SUM(CASE WHEN B-A<=5 THEN 1 ELSE 0 END) AS [0-5],
SUM(CASE WHEN B-A>5 AND B-A<=10 THEN 1 ELSE 0 END) AS [5-10],
SUM(CASE WHEN B-A>10 AND B-A<=20 THEN 1 ELSE 0 END) AS [10-20],
SUM(CASE WHEN B-A>20 AND B-A<=50 THEN 1 ELSE 0 END) AS [20-50],
SUM(CASE WHEN B-A>50 AND B-A<=60 THEN 1 ELSE 0 END) AS [50-60]
FROM TESTDROP TABLE TEST
--得到两个时间的时间差(以秒来计算)
declare @t table (a int ,b int )
insert into @t
select 1,2 union all
select 1,4 union all
select 1,3 union all
select 1,8 union all
select 2,44 union all
select 1,22 union all
select 3,3 union all
select 3,5 union all
select 1,4 union all
select 7,8 union all
select 5,50 select sum(case when b-a>=0 and b-a<5 then 1 else 0 end ) as [0-5]
,sum(case when b-a>=5 and b-a<10 then 1 else 0 end ) as [5-10]
,sum(case when b-a>=10 and b-a<20 then 1 else 0 end ) as [10-20]
,sum(case when b-a>=20 and b-a<50 then 1 else 0 end ) as [20-50]
,sum(case when b-a>=50 and b-a<60 then 1 else 0 end ) as [50-60]
from @t
--结果0-5 5-10 10-20 20-50 50-60
----------- ----------- ----------- ----------- -----------
7 1 0 3 0(所影响的行数为 1 行)
sum(case when datediff(second, a, b) > 0 and datediff(second, a, b) <= 5 then 1 else 0 end) as '0~5',
sum(case when datediff(second, a, b) > 5 and datediff(second, a, b) <= 10 then 1 else 0 end) as '5~10',
sum(case when datediff(second, a, b) > 10 and datediff(second, a, b) <= 20 then 1 else 0 end) as '10~20',
sum(case when datediff(second, a, b) > 20 and datediff(second, a, b) <= 50 then 1 else 0 end) as '20~50',
sum(case when datediff(second, a, b) > 50 and datediff(second, a, b) <= 60 then 1 else 0 end) as '50~60'
from
Table
SUM(CASE WHEN DATEDIFF(s,A,B)<=5 THEN 1 ELSE 0 END) AS [0-5],
SUM(CASE WHEN DATEDIFF(s,A,B)>5 AND DATEDIFF(s,A,B)<=10 THEN 1 ELSE 0 END) AS [5-10],
SUM(CASE WHEN DATEDIFF(s,A,B)>10 AND DATEDIFF(s,A,B)<=20 THEN 1 ELSE 0 END) AS [10-20],
SUM(CASE WHEN DATEDIFF(s,A,B)>20 AND DATEDIFF(s,A,B)<=50 THEN 1 ELSE 0 END) AS [20-50],
SUM(CASE WHEN DATEDIFF(s,A,B)>50 AND DATEDIFF(s,A,B)<=60 THEN 1 ELSE 0 END) AS [50-60]
FROM TEST