换个思路解决SQL经典问题(一):按每半小时统计
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