新手问题：频数统计？
--问题如下？create table #tb([fym] varchar(10),[type] varchar(100))insert into #tb values('201103', 'Online 12'),('201103', 'Silver'),('201103', 'Retail'),('201104', 'Online Non'),('201104', 'Online 12'),('201104', 'NULL'),('201104', 'Online 12'),('201201', 'Retail'),('201201', 'Online 1'),('201202', 'Retail'),('201202', 'Online 12'),('201202', 'Online 12'),('201202', 'NULL'),('201202', 'NULL');/*要求结果： Year Type 01 02 03 04 05 06 07 08 09 10 11 122011 'NULL' 0 0 0 1 0 0 0 0 0 0 0 02011 'Online 1' 0 0 0 0 0 0 0 0 0 0 0 02011 'Online 12' 0 0 1 2 0 0 0 0 0 0 0 02011 'Online Non' 0 0 0 1 0 0 0 0 0 0 0 02011 'Retail' 0 0 1 0 0 0 0 0 0 0 0 02011 'Silver' 0 0 1 0 0 0 0 0 0 0 0 02012 'NULL' 0 2 0 0 0 0 0 0 0 0 0 02012 'Online 1' 1 0 0 0 0 0 0 0 0 0 0 02012 'Online 12' 0 2 0 0 0 0 0 0 0 0 0 02012 'Online Non' 0 0 0 0 0 0 0 0 0 0 0 02012 'Retail' 1 1 0 0 0 0 0 0 0 0 0 02012 'Silver' 0 0 0 0 0 0 0 0 0 0 0 0*/select left(fym,4) as year,type,sum(case when right(fym,2) = '01' then 1 else 0 end) as '01',sum(case when right(fym,2) = '02' then 1 else 0 end) as '02',sum(case when right(fym,2) = '03' then 1 else 0 end) as '03',sum(case when right(fym,2) = '04' then 1 else 0 end) as '04',sum(case when right(fym,2) = '05' then 1 else 0 end) as '05',sum(case when right(fym,2) = '06' then 1 else 0 end) as '06',sum(case when right(fym,2) = '07' then 1 else 0 end) as '07',sum(case when right(fym,2) = '08' then 1 else 0 end) as '08',sum(case when right(fym,2) = '09' then 1 else 0 end) as '09',sum(case when right(fym,2) = '10' then 1 else 0 end) as '10',sum(case when right(fym,2) = '11' then 1 else 0 end) as '11',sum(case when right(fym,2) = '12' then 1 else 0 end) as '12'from #tbgroup by left(fym,4),typeorder by left(fym,4)
[解决办法]
SELECT YEAR,TYPE, isnull([01],0) as [01],isnull([02],0) as [02],isnull([03],0) as [03],isnull([04],0) as [04], isnull([05],0) as [05],isnull([06],0) as [06],isnull([07],0) as [07],isnull([08],0) as [08],isnull([09],0) as [09],isnull([10],0) as [10],isnull([11],0),isnull([12],0)FROM( SELECT YEAR,MONTH,type,cnt=COUNT(1) FROM ( SELECT LEFT(fym,4)AS YEAR,RIGHT(fym,2) AS MONTH, type FROM #tb ) AS t GROUP BY YEAR,MONTH,type) AS tPIVOT( SUM(cnt) FOR t.MONTH IN ([01],[02],[03],[04],[05],[06],[07],[08],[09],[10],[11],[12])) AS porder by year/*YEAR TYPE 01 02 03 04 05 06 07 08 09 10 -------- ---------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------2011 NULL 0 0 0 1 0 0 0 0 0 0 0 02011 Online 12 0 0 1 2 0 0 0 0 0 0 0 02011 Online Non 0 0 0 1 0 0 0 0 0 0 0 02011 Retail 0 0 1 0 0 0 0 0 0 0 0 02011 Silver 0 0 1 0 0 0 0 0 0 0 0 02012 NULL 0 2 0 0 0 0 0 0 0 0 0 02012 Online 1 1 0 0 0 0 0 0 0 0 0 0 02012 Online 12 0 2 0 0 0 0 0 0 0 0 0 02012 Retail 1 1 0 0 0 0 0 0 0 0 0 02012 Silver 0 1 0 0 0 0 0 0 0 0 0 0(10 行受影响)*/