日期:2014-05-18  浏览次数:20353 次

新手问题:频数统计?
SQL code

--问题如下?
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   12
2011    'NULL'        0    0    0     1      0       0    0    0      0       0    0    0
2011   'Online 1'     0    0    0     0      0       0    0    0      0       0    0    0
2011  'Online 12'     0    0    1     2      0       0    0    0      0       0    0    0
2011  'Online Non'    0    0    0     1      0       0    0    0      0       0    0    0
2011   'Retail'       0    0    1     0      0       0    0    0      0       0    0    0
2011   'Silver'       0    0    1     0      0       0    0    0      0       0    0    0
2012    'NULL'        0    2    0     0      0       0    0    0      0       0    0    0
2012   'Online 1'     1    0    0     0      0       0    0    0      0       0    0    0
2012  'Online 12'     0    2    0     0      0       0    0    0      0       0    0    0
2012  'Online Non'    0    0    0     0      0       0    0    0      0       0    0    0
2012   'Retail'       1    1    0     0      0       0    0    0      0       0    0    0
2012   'Silver'       0    0    0     0      0       0    0    0      0       0    0    0
*/



------解决方案--------------------
SQL code

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 #tb
group by left(fym,4),type
order by left(fym,4)

------解决方案--------------------
SQL code

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 t
PIVOT
(
    SUM(cnt) FOR t.MONTH IN ([01],[02],[03],[04],[05],[06],[07],[08],[09],[10],[11],[12])
) AS p
order 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           0
2011     Online 12  0           0           1           2           0           0           0           0           0           0