select cjid, sum(case when month(out)=1 and out is not null then 1 else 0 end) as '1月份', sum(case when month(out)=2 and out is not null then 1 else 0 end) as '2月份', sum(case when month(out)=3 and out is not null then 1 else 0 end) as '3月份', ... from tb where year(out)=2014 group by cjid中间的自己去补一下因为只有12个月 直接写成静态了算了
select gwid, sum(case when MONTH(out)=1 then 1 else 0 end) '1月', --... sum(case when MONTH(out)=12 then 1 else 0 end) '12月' from #tm where year(out)='2014' group by gwid
select gwid, COUNT(CASE WHEN MONTH(out)=1 THEN out END)[1月], COUNT(CASE WHEN MONTH(out)=2 THEN out END)[2月], COUNT(CASE WHEN MONTH(out)=3 THEN out END)[3月], COUNT(CASE WHEN MONTH(out)=4 THEN out END)[4月], COUNT(CASE WHEN MONTH(out)=5 THEN out END)[5月], COUNT(CASE WHEN MONTH(out)=6 THEN out END)[6月], COUNT(CASE WHEN MONTH(out)=7 THEN out END)[7月], COUNT(CASE WHEN MONTH(out)=8 THEN out END)[8月], COUNT(CASE WHEN MONTH(out)=9 THEN out END)[9月], COUNT(CASE WHEN MONTH(out)=10 THEN out END)[10月], COUNT(CASE WHEN MONTH(out)=11 THEN out END)[11月], COUNT(CASE WHEN MONTH(out)=12 THEN out END)[12月] FROM TB WHERE year(out)='2014' GROUP BY gwid
cjid,
sum(case when month(out)=1 and out is not null then 1 else 0 end) as '1月份',
sum(case when month(out)=2 and out is not null then 1 else 0 end) as '2月份',
sum(case when month(out)=3 and out is not null then 1 else 0 end) as '3月份',
...
from
tb
where
year(out)=2014
group by
cjid中间的自己去补一下因为只有12个月 直接写成静态了算了
select
gwid,
sum(case when MONTH(out)=1 then 1 else 0 end) '1月',
--...
sum(case when MONTH(out)=12 then 1 else 0 end) '12月'
from #tm
where year(out)='2014'
group by gwid
gwid,
COUNT(CASE WHEN MONTH(out)=1 THEN out END)[1月],
COUNT(CASE WHEN MONTH(out)=2 THEN out END)[2月],
COUNT(CASE WHEN MONTH(out)=3 THEN out END)[3月],
COUNT(CASE WHEN MONTH(out)=4 THEN out END)[4月],
COUNT(CASE WHEN MONTH(out)=5 THEN out END)[5月],
COUNT(CASE WHEN MONTH(out)=6 THEN out END)[6月],
COUNT(CASE WHEN MONTH(out)=7 THEN out END)[7月],
COUNT(CASE WHEN MONTH(out)=8 THEN out END)[8月],
COUNT(CASE WHEN MONTH(out)=9 THEN out END)[9月],
COUNT(CASE WHEN MONTH(out)=10 THEN out END)[10月],
COUNT(CASE WHEN MONTH(out)=11 THEN out END)[11月],
COUNT(CASE WHEN MONTH(out)=12 THEN out END)[12月]
FROM TB
WHERE year(out)='2014'
GROUP BY gwid