????????SQL?????????????????????????????

???????????????

CREATE TABLE [dbo].[E_Classes] ([ClassID] varchar(12) NOT NULL ,[BeginDate] date NULL ,[EndDate] date NULL ,[StudentCount] smallint NULL);
INSERT INTO [dbo].[E_Classes] ([ClassID], [BeginDate], [EndDate], [StudentCount]) VALUES (N'01', N'2011-10-07', N'2012-03-11', N'49');
INSERT INTO [dbo].[E_Classes] ([ClassID], [BeginDate], [EndDate], [StudentCount]) VALUES (N'02', N'2012-04-05', N'2012-06-20', N'23');
INSERT INTO [dbo].[E_Classes] ([ClassID], [BeginDate], [EndDate], [StudentCount]) VALUES (N'03', N'2012-05-01', N'2012-07-03', N'46');
INSERT INTO [dbo].[E_Classes] ([ClassID], [BeginDate], [EndDate], [StudentCount]) VALUES (N'04', N'2013-03-08', N'2013-06-12', N'42');
INSERT INTO [dbo].[E_Classes] ([ClassID], [BeginDate], [EndDate], [StudentCount]) VALUES (N'05', N'2013-09-19', N'2013-10-26', N'78');
INSERT INTO [dbo].[E_Classes] ([ClassID], [BeginDate], [EndDate], [StudentCount]) VALUES (N'06', N'2013-11-21', N'2014-01-09', N'44');
INSERT INTO [dbo].[E_Classes] ([ClassID], [BeginDate], [EndDate], [StudentCount]) VALUES (N'07', N'2013-12-27', N'2014-02-06', N'36');
INSERT INTO [dbo].[E_Classes] ([ClassID], [BeginDate], [EndDate], [StudentCount]) VALUES (N'08', N'2014-02-28', N'2014-06-20', N'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????

?????????????????????????????????:

CREATE TABLE [dbo].[E_Sequence]([Sequence_ID] [int] IDENTITY(1,1) NOT NULL, CONSTRAINT [PK_E_Sequence] PRIMARY KEY CLUSTERED ([Sequence_ID] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]);
SET IDENTITY_INSERT [dbo].[E_Sequence] ON
INSERT INTO [dbo].[E_Sequence] (Sequence_ID)
SELECT number FROM [master]..[spt_values] WHERE  type = 'P'
UNION ALL
SELECT number + 2048 FROM [master]..[spt_values] WHERE  type = 'P'
SET IDENTITY_INSERT [dbo].[E_Sequence] OFF

??????????????????????????

SELECT Dateadd(day, Sequence_id, '2011-1-1') AS Class_Date FROM E_Sequence

????????????????2011?01?01????4000?????????????????????????????

SELECT Year(Class_Date)  AS Class_Year,
       Month(Class_Date) AS Class_Month,
       StudentCount
FROM   (SELECT Dateadd(day, Sequence_id, '2011-1-1') AS Class_Date
        FROM   E_Sequence) t1,
       E_Classes t2
WHERE  t1.Class_Date BETWEEN t2.BeginDate AND t2.EndDate

?????

OK??????????????“?”?????“?”?????“???”????????????????????????????SQL2000?????Case When End??

SELECT Class_Year      AS ??,
       Isnull([1], 0)  AS ??,
       Isnull([2], 0)  AS ??,
       Isnull([3], 0)  AS ??,
       Isnull([4], 0)  AS ??,
       Isnull([5], 0)  AS ??,
       Isnull([6], 0)  AS ??,
       Isnull([7], 0)  AS ??,
       Isnull([8], 0)  AS ??,
       Isnull([9], 0)  AS ??,
       Isnull([10], 0) AS ??,
       Isnull([11], 0) AS ???,
       Isnull([12], 0) AS ???
FROM   (SELECT Year(Class_Date)  AS Class_Year,
               Month(Class_Date) AS Class_Month,
               studentcount
        FROM   (SELECT Dateadd(day, Sequence_id, '2011-1-1') AS Class_Date
                FROM   E_Sequence) t1,
               e_classes t2
        WHERE  t1.Class_Date BETWEEN t2.begindate AND t2.enddate) t 
        PIVOT (Sum(studentcount) FOR Class_Month IN
        ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12])) tpivot

???????????????????????????

???

SELECT Dateadd(day, Sequence_id, '2011-1-1') AS Class_Date FROM E_Sequence

????????????????????????????????????????????????

???????

????2012?07?16??2013?06?15??????????????????