22,206
社区成员
发帖
与我相关
我的任务
分享
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
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 行)
*/