求个按一年十二个月统计的sql语句


CREATE TABLE [dt_user_business] ([id] [int]  IDENTITY (1, 1)  NOT NULL,[unit_id] [int]  NULL,[wicket_id] [int]  NULL,[user_name] [nvarchar]  (50) NULL,[user_id_number] [nvarchar]  (50) NULL,[user_phone] [nvarchar]  (50) NULL,[business_no] [nvarchar]  (50) NULL,[business_name] [nvarchar]  (255) NULL,[business_content] [nvarchar]  (500) NULL,[business_time] [datetime]  NULL,[evaluate] [int]  NULL,[idea] [nvarchar]  (500) NULL)
ALTER TABLE [dt_user_business] WITH NOCHECK ADD  CONSTRAINT [PK_dt_user_business] PRIMARY KEY  NONCLUSTERED ( [id] )
SET IDENTITY_INSERT [dt_user_business] ONINSERT [dt_user_business] ([id],[unit_id],[wicket_id],[user_name],[user_id_number],[user_phone],[business_time],[evaluate]) VALUES ( 41482,3,11,N'小华',N'4.13026199005236E+17',N'18758034485',N'2013/4/7 14:37:03',1)
INSERT [dt_user_business] ([id],[unit_id],[wicket_id],[user_name],[user_id_number],[user_phone],[business_time],[evaluate]) VALUES ( 41573,2,8,N'小华',N'4.13026199005236E+17',N'18758034485',N'2013/4/7 14:37:03',3)
INSERT [dt_user_business] ([id],[unit_id],[wicket_id],[user_name],[user_id_number],[user_phone],[business_time],[evaluate]) VALUES ( 41709,3,11,N'小华',N'4.13026199005236E+17',N'18758034485',N'2013/4/7 14:37:03',1)
INSERT [dt_user_business] ([id],[unit_id],[wicket_id],[user_name],[user_id_number],[user_phone],[business_time],[evaluate]) VALUES ( 41775,3,10,N'小华',N'4.13026199005236E+17',N'18758034485',N'2013/4/7 14:37:04',3)
INSERT [dt_user_business] ([id],[unit_id],[wicket_id],[user_name],[user_id_number],[user_phone],[business_time],[evaluate]) VALUES ( 41809,3,10,N'小明',N'4.13026199005236E+17',N'18758034485',N'2013/4/7 14:37:04',4)
INSERT [dt_user_business] ([id],[unit_id],[wicket_id],[user_name],[user_id_number],[user_phone],[business_time],[evaluate]) VALUES ( 41855,1,5,N'小张',N'4.13026199005236E+17',N'18758034485',N'2013/4/7 14:37:04',1)
INSERT [dt_user_business] ([id],[unit_id],[wicket_id],[user_name],[user_id_number],[user_phone],[business_time],[evaluate]) VALUES ( 41881,2,8,N'小明',N'4.13026199005236E+17',N'18758034485',N'2013/4/7 14:37:04',4)
INSERT [dt_user_business] ([id],[unit_id],[wicket_id],[user_name],[user_id_number],[user_phone],[business_time],[evaluate]) VALUES ( 41899,2,9,N'小华',N'4.13026199005236E+17',N'18758034485',N'2013/4/7 14:37:04',3)
INSERT [dt_user_business] ([id],[unit_id],[wicket_id],[user_name],[user_id_number],[user_phone],[business_time],[evaluate]) VALUES ( 41919,1,5,N'小华',N'4.13026199005236E+17',N'18758034485',N'2013/4/7 14:37:04',3)
INSERT [dt_user_business] ([id],[unit_id],[wicket_id],[user_name],[user_id_number],[user_phone],[business_time],[evaluate]) VALUES ( 41922,3,11,N'小华',N'4.13026199005236E+17',N'18758034485',N'2013/4/7 14:37:04',1)SET IDENTITY_INSERT [dt_user_business] OFF年统计：
select unit_id,wicket_id,count(evaluate) as totalize, evaluate from dt_user_business 
where unit_id=1 and  year(business_time) = '2013'
group by evaluate,unit_id,wicket_id  
order by wicket_id,evaluate
返回查询：
现在求每年中十二个月的统计数据？
十二个月中每天的数据统计？望请各位大虾
sql

解决方案 »

  1.   

    select unit_id,wicket_id,evaluate,
           sum(case when unit_id = 1  then 1 else 0 end) totalize_1,
           sum(case when unit_id = 2  then 1 else 0 end) totalize_2,
           sum(case when unit_id = 3  then 1 else 0 end) totalize_3,
           sum(case when unit_id = 4  then 1 else 0 end) totalize_4,
           sum(case when unit_id = 5  then 1 else 0 end) totalize_5,
           sum(case when unit_id = 6  then 1 else 0 end) totalize_6,
           sum(case when unit_id = 7  then 1 else 0 end) totalize_7,
           sum(case when unit_id = 8  then 1 else 0 end) totalize_8,
           sum(case when unit_id = 9  then 1 else 0 end) totalize_9,
           sum(case when unit_id = 10 then 1 else 0 end) totalize_10,
           sum(case when unit_id = 11 then 1 else 0 end) totalize_11,
           sum(case when unit_id = 12 then 1 else 0 end) totalize_12
    from dt_user_business 
    where year(business_time) = '2013'
    group by evaluate,unit_id,wicket_id  
    order by wicket_id,evaluate/*
    unit_id     wicket_id   evaluate    totalize_1  totalize_2  totalize_3  totalize_4  totalize_5  totalize_6  totalize_7  totalize_8  totalize_9  totalize_10 totalize_11 totalize_12 
    ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- 
    1           5           1           1           0           0           0           0           0           0           0           0           0           0           0
    1           5           3           1           0           0           0           0           0           0           0           0           0           0           0
    2           8           3           0           1           0           0           0           0           0           0           0           0           0           0
    2           8           4           0           1           0           0           0           0           0           0           0           0           0           0
    2           9           3           0           1           0           0           0           0           0           0           0           0           0           0
    3           10          3           0           0           1           0           0           0           0           0           0           0           0           0
    3           10          4           0           0           1           0           0           0           0           0           0           0           0           0
    3           11          1           0           0           3           0           0           0           0           0           0           0           0           0（所影响的行数为 8 行）*/
      

  2.   

    十二个月中每天的数据统计？哪个字段能体现你的日期数据?wicket_id   ?
      

  3.   

    CREATE TABLE [dbo].[dt_user_business](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [unit_id] [int] NULL,
    [wicket_id] [int] NULL,
    [user_name] [nvarchar](50) NULL,
    [user_id_number] [nvarchar](50) NULL,
    [user_phone] [nvarchar](50) NULL,
    [business_no] [nvarchar](50) NULL,
    [business_name] [nvarchar](255) NULL,
    [business_content] [nvarchar](500) NULL,
    [business_time] [datetime] NULL,
    [evaluate] [int] NULL,
    [idea] [nvarchar](500) NULL)business_time 这个字段是日期字段
      

  4.   

    月份 也是按照business_time 这个字段? 
      

  5.   

    年统计、月统计都是按 business_time 作数据统计
      

  6.   

    --按照月
    select unit_id,wicket_id,evaluate,
           sum(case when datepart(mm,business_time) = 1  then 1 else 0 end) totalize_1,
           sum(case when datepart(mm,business_time) = 2  then 1 else 0 end) totalize_2,
           sum(case when datepart(mm,business_time) = 3  then 1 else 0 end) totalize_3,
           sum(case when datepart(mm,business_time) = 4  then 1 else 0 end) totalize_4,
           sum(case when datepart(mm,business_time) = 5  then 1 else 0 end) totalize_5,
           sum(case when datepart(mm,business_time) = 6  then 1 else 0 end) totalize_6,
           sum(case when datepart(mm,business_time) = 7  then 1 else 0 end) totalize_7,
           sum(case when datepart(mm,business_time) = 8  then 1 else 0 end) totalize_8,
           sum(case when datepart(mm,business_time) = 9  then 1 else 0 end) totalize_9,
           sum(case when datepart(mm,business_time) = 10 then 1 else 0 end) totalize_10,
           sum(case when datepart(mm,business_time) = 11 then 1 else 0 end) totalize_11,
           sum(case when datepart(mm,business_time) = 12 then 1 else 0 end) totalize_12
    from dt_user_business 
    where unit_id = 1 and year(business_time) = '2013'
    group by evaluate,unit_id,wicket_id  
    order by wicket_id,evaluate/*
    unit_id     wicket_id   evaluate    totalize_1  totalize_2  totalize_3  totalize_4  totalize_5  totalize_6  totalize_7  totalize_8  totalize_9  totalize_10 totalize_11 totalize_12 
    ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- 
    1           5           1           0           0           0           1           0           0           0           0           0           0           0           0
    1           5           3           0           0           0           1           0           0           0           0           0           0           0           0（所影响的行数为 2 行）*/
    --按照日期.
    select unit_id,wicket_id,evaluate,datepart(mm,business_time) mm,
           sum(case when datepart(dd,business_time) = 1  then 1 else 0 end) totalize_1,
           sum(case when datepart(dd,business_time) = 2  then 1 else 0 end) totalize_2,
           ...
           sum(case when datepart(mm,business_time) = 31 then 1 else 0 end) totalize_31
    from dt_user_business 
    where unit_id = 1 and year(business_time) = '2013'
    group by evaluate,unit_id,wicket_id  
    order by wicket_id,evaluate
      

  7.   


    INSERT [dt_user_business] ([id],[unit_id],[wicket_id],[user_name],[user_id_number],[user_phone],[business_time],[evaluate]) VALUES ( 42105,2,9,N'小明',N'4.13026199005236E+17',N'18758034485',N'2013/4/7 15:09:48',5)
    INSERT [dt_user_business] ([id],[unit_id],[wicket_id],[user_name],[user_id_number],[user_phone],[business_time],[evaluate]) VALUES ( 42106,2,8,N'小明',N'4.13026199005236E+17',N'18758034485',N'2013/4/7 15:09:48',1)
    INSERT [dt_user_business] ([id],[unit_id],[wicket_id],[user_name],[user_id_number],[user_phone],[business_time],[evaluate]) VALUES ( 42107,2,8,N'小华',N'4.13026199005236E+17',N'18758034485',N'2013/4/7 15:09:48',2)
    INSERT [dt_user_business] ([id],[unit_id],[wicket_id],[user_name],[user_id_number],[user_phone],[business_time],[evaluate]) VALUES ( 42108,2,8,N'小华',N'4.13026199005236E+17',N'18758034485',N'2013/4/7 15:09:48',3)
    INSERT [dt_user_business] ([id],[unit_id],[wicket_id],[user_name],[user_id_number],[user_phone],[business_time],[evaluate]) VALUES ( 42109,2,8,N'小明',N'4.13026199005236E+17',N'18758034485',N'2013/4/7 15:09:48',4)
    INSERT [dt_user_business] ([id],[unit_id],[wicket_id],[user_name],[user_id_number],[user_phone],[business_time],[evaluate]) VALUES ( 42114,2,7,N'小明',N'4.13026199005236E+17',N'18758034485',N'2013/4/7 15:09:48',4)
    INSERT [dt_user_business] ([id],[unit_id],[wicket_id],[user_name],[user_id_number],[user_phone],[business_time],[evaluate]) VALUES ( 42115,2,7,N'小明',N'4.13026199005236E+17',N'18758034485',N'2013/4/7 15:09:48',5)
    INSERT [dt_user_business] ([id],[unit_id],[wicket_id],[user_name],[user_id_number],[user_phone],[business_time],[evaluate]) VALUES ( 42123,3,10,N'小明',N'4.13026199005236E+17',N'18758034485',N'2013/4/7 15:09:48',4)
    INSERT [dt_user_business] ([id],[unit_id],[wicket_id],[user_name],[user_id_number],[user_phone],[business_time],[evaluate]) VALUES ( 42124,3,10,N'小明',N'4.13026199005236E+17',N'18758034485',N'2013/4/7 15:09:48',5)
    INSERT [dt_user_business] ([id],[unit_id],[wicket_id],[user_name],[user_id_number],[user_phone],[business_time],[evaluate]) VALUES ( 42125,2,9,N'小华',N'4.13026199005236E+17',N'18758034485',N'2013/4/7 15:09:48',1)
    INSERT [dt_user_business] ([id],[unit_id],[wicket_id],[user_name],[user_id_number],[user_phone],[business_time],[evaluate]) VALUES ( 42126,2,9,N'小华',N'4.13026199005236E+17',N'18758034485',N'2013/4/7 15:09:48',2)
    INSERT [dt_user_business] ([id],[unit_id],[wicket_id],[user_name],[user_id_number],[user_phone],[business_time],[evaluate]) VALUES ( 42142,1,6,N'小华',N'4.13026199005236E+17',N'18758034485',N'2013/4/7 15:09:49',3)
    INSERT [dt_user_business] ([id],[unit_id],[wicket_id],[user_name],[user_id_number],[user_phone],[business_time],[evaluate]) VALUES ( 42143,1,6,N'小明',N'4.13026199005236E+17',N'18758034485',N'2013/4/7 15:09:49',4)
    INSERT [dt_user_business] ([id],[unit_id],[wicket_id],[user_name],[user_id_number],[user_phone],[business_time],[evaluate]) VALUES ( 42144,1,6,N'小明',N'4.13026199005236E+17',N'18758034485',N'2013/4/7 15:09:49',5)
    INSERT [dt_user_business] ([id],[unit_id],[wicket_id],[user_name],[user_id_number],[user_phone],[business_time],[evaluate]) VALUES ( 42145,1,5,N'小张',N'4.13026199005236E+17',N'18758034485',N'2013/4/7 15:09:49',1)
    INSERT [dt_user_business] ([id],[unit_id],[wicket_id],[user_name],[user_id_number],[user_phone],[business_time],[evaluate]) VALUES ( 42146,1,5,N'小华',N'4.13026199005236E+17',N'18758034485',N'2013/4/7 15:09:49',2)
    INSERT [dt_user_business] ([id],[unit_id],[wicket_id],[user_name],[user_id_number],[user_phone],[business_time],[evaluate]) VALUES ( 42147,1,5,N'小华',N'4.13026199005236E+17',N'18758034485',N'2013/4/7 15:09:49',3)
    INSERT [dt_user_business] ([id],[unit_id],[wicket_id],[user_name],[user_id_number],[user_phone],[business_time],[evaluate]) VALUES ( 42148,1,5,N'小明',N'4.13026199005236E+17',N'18758034485',N'2013/4/7 15:09:49',4)