声明：本篇文章的SQL语句为了体现作者的思路，并非最优，请根据实际需要进行优化。
在开发的过程中，我们常常会遇到类似这种按每半小时统计数量的需求：
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);
只统计其中的一部分并不难：
SELECT CallInTime_Hour,
CallInTime_Minute,
Count(*) AS CallInTime_Count
FROM (SELECT Datepart(HOUR, CallInTime) AS CallInTime_Hour,
Datepart(MINUTE, CallInTime) / 30 * 30 AS CallInTime_Minute
/*利用SQL 整型/整型=整型 的特性，将时间分组。*/
FROM [PerHalfHour]
WHERE CallInTime >= '2014-01-01'
AND CallInTime < '2014-01-02') t
GROUP BY CallInTime_Hour,
CallInTime_Minute
ORDER BY CallInTime_Hour,
CallInTime_Minute
统计结果为：
但是实际需求可能要求显示全部24小时的统计，这时我们只要加一张外联表就可以了：
CREATE TABLE [dbo].[Sequence_HalfHour]([sequence_time] [nvarchar](20) NULL,[sequence_hour] [int] NULL,[sequence_minute] [int] NOT NULL); INSERT [dbo].[Sequence_HalfHour] ([sequence_time], [sequence_hour], [sequence_minute]) VALUES (N'00:00:00~00:29:59', 0, 0); INSERT [dbo].[Sequence_HalfHour] ([sequence_time], [sequence_hour], [sequence_minute]) VALUES (N'00:30:00~00:59:59', 0, 30); INSERT [dbo].[Sequence_HalfHour] ([sequence_time], [sequence_hour], [sequence_minute]) VALUES (N'01:00:00~01:29:59', 1, 0); INSERT [dbo].[Sequence_HalfHour] ([sequence_time], [sequence_hour], [sequence_minute]) VALUES (N'01:30:00~01:59:59', 1, 30); ... INSERT [dbo].[Sequence_HalfHour] ([sequence_time], [sequence_hour], [sequence_minute]) VALUES (N'23:00:00~23:29:59', 23, 0); INSERT [dbo].[Sequence_HalfHour] ([sequence_time], [sequence_hour], [sequence_minute]) VALUES (N'23:30:00~23:59:59', 23, 30);
然后用这个外联表去Left Join上一个查询就OK：
SELECT t0.sequence_time AS 时间段,
Isnull(CallInTime_Count, 0) AS 数量
FROM sequence_halfhour t0
LEFT JOIN (SELECT CallInTime_Hour,
CallInTime_Minute,
Count(*) AS CallInTime_Count
FROM (SELECT Datepart(HOUR, CallInTime) AS CallInTime_Hour,
Datepart(MINUTE, CallInTime) / 30 * 30 AS CallInTime_Minute
/*利用SQL 整型/整型=整型 的特性，将时间分组。*/
FROM [PerHalfHour]
WHERE CallInTime >= '2014-01-01'
AND CallInTime < '2014-01-02') t
GROUP BY CallInTime_Hour,
CallInTime_Minute) t1
ON t0.sequence_hour = t1.CallInTime_Hour
AND t0.sequence_minute = t1.CallInTime_Minute
查看结果：
留个尾巴：
第一张表里有个Result字段，请用SQL语句输出以下表格：
