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

pboy2925 2014-02-07 02:37:57

加精

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

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

建表语句Transact-SQL

1
2
3
4
5
6
7
8
9
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人天次。
同样的，换个思路，可能解决起来会更简单一些。
查看解决方法 http://jorkin.me/classic-sql-question-2/

...全文