[抛砖引玉]换个思路解决SQL经典问题（二）：时间区间按年月统计

[pboy2925]

声明：本篇文章的SQL语句为了体现作者的思路，并非最优，请根据实际需要进行优化。

曾经帮别人解决一个这样的问题：

CREATE TABLE [dbo].[E_Classes]( [ClassID] [varchar](12) NOT NULL, [BeginDate] [smalldatetime] NULL, [EndDate] [smalldatetime] NULL, [StudentCount] [smallint] NULL);
INSERT [dbo].[E_Classes] ([ClassID], [BeginDate], [EndDate], [StudentCount]) VALUES (N'01', CAST(0x9F750000 AS SmallDateTime), CAST(0xA0110000 AS SmallDateTime), 49);
INSERT [dbo].[E_Classes] ([ClassID], [BeginDate], [EndDate], [StudentCount]) VALUES (N'02', CAST(0xA02A0000 AS SmallDateTime), CAST(0xA0760000 AS SmallDateTime), 23);
INSERT [dbo].[E_Classes] ([ClassID], [BeginDate], [EndDate], [StudentCount]) VALUES (N'03', CAST(0xA0440000 AS SmallDateTime), CAST(0xA0830000 AS SmallDateTime), 46);
INSERT [dbo].[E_Classes] ([ClassID], [BeginDate], [EndDate], [StudentCount]) VALUES (N'04', CAST(0xA17B0000 AS SmallDateTime), CAST(0xA1DB0000 AS SmallDateTime), 42);
INSERT [dbo].[E_Classes] ([ClassID], [BeginDate], [EndDate], [StudentCount]) VALUES (N'05', CAST(0xA23E0000 AS SmallDateTime), CAST(0xA2630000 AS SmallDateTime), 78);
INSERT [dbo].[E_Classes] ([ClassID], [BeginDate], [EndDate], [StudentCount]) VALUES (N'06', CAST(0xA27D0000 AS SmallDateTime), CAST(0xA2AE0000 AS SmallDateTime), 44);
INSERT [dbo].[E_Classes] ([ClassID], [BeginDate], [EndDate], [StudentCount]) VALUES (N'07', CAST(0xA2A10000 AS SmallDateTime), CAST(0xA2CA0000 AS SmallDateTime), 36);
INSERT [dbo].[E_Classes] ([ClassID], [BeginDate], [EndDate], [StudentCount]) VALUES (N'08', CAST(0xA2E00000 AS SmallDateTime), CAST(0xA3500000 AS SmallDateTime), 52);

计算方法为 （每门课在当月的有效天数*人数）的总和，如2014年01月的应该为 课程06的 2014-01-01~2014-01-09共计9天*44人=396人天 再加上 课程07的2014-01-01~2014-01-31共计31天*36人=1116人天，那么2014年01月的人天为 396+1116=1512人天次。

[發糞塗牆]

推荐一下也可以。

[pboy2925]

版主请删除此贴吧，发的时间没编辑完，按到回车上去了。不知道为什么就再也不能修改了。

[發糞塗牆]

额，我不是这个板块的，改不了

[IT_lau]

0xA23E0000 这个是啥？