换个思路解决SQL经典问题（一）：按每半小时统计

[hilex_jay]

http://bbs.csdn.net/topics/390704007
闲来无事逛逛论坛看到这个帖子感觉挺有意思，按照自己方法做了一遍，分享给各位。
在开发的过程中，我们常常会遇到类似这种按每半小时统计数量的需求：

CREATE TABLE [dbo].[PerHalfHour]([CallInTime] [smalldatetime] NULL, [Result] [int] NULL);
INSERT [dbo].[PerHalfHour] ([CallInTime], [Result]) VALUES ('2014-01-01 08:20:00', 2);
INSERT [dbo].[PerHalfHour] ([CallInTime], [Result]) VALUES ('2014-01-01 08:38:00', 2);
INSERT [dbo].[PerHalfHour] ([CallInTime], [Result]) VALUES ('2014-01-01 08:43:00', 2);
INSERT [dbo].[PerHalfHour] ([CallInTime], [Result]) VALUES ('2014-01-01 08:49:00', 2);
INSERT [dbo].[PerHalfHour] ([CallInTime], [Result]) VALUES ('2014-01-01 09:07:00', 2);
INSERT [dbo].[PerHalfHour] ([CallInTime], [Result]) VALUES ('2014-01-01 09:07:00', 1);
INSERT [dbo].[PerHalfHour] ([CallInTime], [Result]) VALUES ('2014-01-01 09:10:00', 1);
INSERT [dbo].[PerHalfHour] ([CallInTime], [Result]) VALUES ('2014-01-01 09:16:00', 1);
INSERT [dbo].[PerHalfHour] ([CallInTime], [Result]) VALUES ('2014-01-01 09:41:00', 1);
INSERT [dbo].[PerHalfHour] ([CallInTime], [Result]) VALUES ('2014-01-01 09:44:00', 1);
INSERT [dbo].[PerHalfHour] ([CallInTime], [Result]) VALUES ('2014-01-01 09:52:00', 1);

要求实现：
stime etime number
00:00:00.0000000 00:29:59.0000000 0
00:30:00.0000000 00:59:59.0000000 0
01:00:00.0000000 01:29:59.0000000 0
01:30:00.0000000 01:59:59.0000000 0
02:00:00.0000000 02:29:59.0000000 0
02:30:00.0000000 02:59:59.0000000 0
03:00:00.0000000 03:29:59.0000000 0
03:30:00.0000000 03:59:59.0000000 0
04:00:00.0000000 04:29:59.0000000 0
04:30:00.0000000 04:59:59.0000000 0
05:00:00.0000000 05:29:59.0000000 0
05:30:00.0000000 05:59:59.0000000 0
06:00:00.0000000 06:29:59.0000000 0
06:30:00.0000000 06:59:59.0000000 0
07:00:00.0000000 07:29:59.0000000 0
07:30:00.0000000 07:59:59.0000000 0
08:00:00.0000000 08:29:59.0000000 1
08:30:00.0000000 08:59:59.0000000 3
09:00:00.0000000 09:29:59.0000000 4
09:30:00.0000000 09:59:59.0000000 3
10:00:00.0000000 10:29:59.0000000 0
10:30:00.0000000 10:59:59.0000000 0
11:00:00.0000000 11:29:59.0000000 0
11:30:00.0000000 11:59:59.0000000 0
12:00:00.0000000 12:29:59.0000000 0
12:30:00.0000000 12:59:59.0000000 0
13:00:00.0000000 13:29:59.0000000 0
13:30:00.0000000 13:59:59.0000000 0
14:00:00.0000000 14:29:59.0000000 0
14:30:00.0000000 14:59:59.0000000 0
15:00:00.0000000 15:29:59.0000000 0
15:30:00.0000000 15:59:59.0000000 0
16:00:00.0000000 16:29:59.0000000 0
16:30:00.0000000 16:59:59.0000000 0
17:00:00.0000000 17:29:59.0000000 0
17:30:00.0000000 17:59:59.0000000 0
18:00:00.0000000 18:29:59.0000000 0
18:30:00.0000000 18:59:59.0000000 0
19:00:00.0000000 19:29:59.0000000 0
19:30:00.0000000 19:59:59.0000000 0
20:00:00.0000000 20:29:59.0000000 0
20:30:00.0000000 20:59:59.0000000 0
21:00:00.0000000 21:29:59.0000000 0
21:30:00.0000000 21:59:59.0000000 0
22:00:00.0000000 22:29:59.0000000 0
22:30:00.0000000 22:59:59.0000000 0
23:00:00.0000000 23:29:59.0000000 0
23:30:00.0000000 23:59:59.0000000 0

[hilex_jay]

代码如下：

with cte as
(
select 
  cast(DATEADD(MINUTE,B.number*30,a.dday) as time) stime,
  cast(DATEADD(SECOND,-1,DATEADD(MINUTE,(B.number+1)*30,a.dday)) as time) etime 
from 
  (select top 1 convert(varchar(10),CallInTime,120) dday from [PerHalfHour]) a
  cross join master..spt_values b 
where b.type = 'P' and number <=48
)
select 
  stime,
  etime,
  sum(number) number 
from
  (
   select stime,etime,case when CallInTime is null then 0 else 1 end number 
   from 
     cte a
     left join [PerHalfHour] b on cast(b.CallInTime as time) between stime and etime
  )a
group by stime,etime

[还在加载中灬]

大部分也是连接数字辅助表实现 的~