一道SQL统计试题

根据上图A表和B表，按照年份和地区生成1至12个月的数据，结果如下：

方法一：

select YEAR,AreaName,
MAX(case Month when '1' then Money else 0 end) as [1月],
MAX(case Month when '2' then Money else 0 end) as [2月],
MAX(case Month when '3' then Money else 0 end) as [3月],
MAX(case Month when '4' then Money else 0 end) as [4月],
MAX(case Month when '5' then Money else 0 end) as [5月],
MAX(case Month when '6' then Money else 0 end) as [6月],
MAX(case Month when '7' then Money else 0 end) as [7月],
MAX(case Month when '8' then Money else 0 end) as [8月],
MAX(case Month when '9' then Money else 0 end) as [9月],
MAX(case Month when '10' then Money else 0 end) as [10月],
MAX(case Month when '11' then Money else 0 end) as [11月],
MAX(case Month when '12' then Money else 0 end) as [12月]
from 
(
select B.AreaName, 
SUM(Money) as Money,YEAR(CreateOn) Year,Month(CreateOn) Month
from A left join B on A.AreaId=B.AreaId
group by AreaName, YEAR(CreateOn),Month(CreateOn)
) A group by Year,AreaName
order by Year,AreaName

方法二：

select Year,AreaName,
ISNULL([1],0) as [1月],ISNULL([2],0) as [2月],ISNULL([3],0) as [3月],ISNULL([4],0) as [4月],
ISNULL([5],0) as [5月],ISNULL([6],0) as [6月],ISNULL([7],0) as [7月],ISNULL([8],0) as [8月],
ISNULL([9],0) as [9月],ISNULL([10],0) as [10月],ISNULL([11],0) as [11月],ISNULL([12],0) as [12月]
from 
(
select B.AreaName, 
SUM(Money) as Money,YEAR(CreateOn) Year,Month(CreateOn) Month
from A left join B on A.AreaId=B.AreaId
group by AreaName, YEAR(CreateOn),Month(CreateOn)
)
A 
pivot
(
	sum(money)
	for Month in
	([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12]) 
) as pvt
order by Year,AreaName

方法三：

--先创建表变量，并插入月份数据
declare @tb table(Month varchar(2)) 
insert @tb select 1 union all select 2 union all select 3 union all select 4 union all
select 5 union all select 6 union all select 7 union all select 8 union all
select 9 union all select 10 union all select 11 union all select 12

declare @sql varchar(8000)
set @sql = 'select Year,AreaName '
select @sql = @sql + ' , max(case Month when ''' + convert(varchar(10),Month) + ''' then Money else 0 end) [' + convert(varchar(10),Month) + '月]'
from 
(
   select Month from @tb
   --select distinct Month(CreateOn) Month from A 
) as a
set @sql = @sql + '  from 
(
  select B.AreaName, 
  SUM(Money) as Money,YEAR(CreateOn) Year,Month(CreateOn) Month
  from A left join B on A.AreaId=B.AreaId
  group by AreaName, YEAR(CreateOn),Month(CreateOn)
)
tb group by Year,AreaName'
--print @sql
exec(@sql)