1、
select
a.CustomerID,
Month01 = sum(case month(a.CreateDate) when 1 then b.Amt else 0 end),
Month02 = sum(case month(a.CreateDate) when 2 then b.Amt else 0 end),
Month03 = sum(case month(a.CreateDate) when 3 then b.Amt else 0 end),
Month04 = sum(case month(a.CreateDate) when 4 then b.Amt else 0 end),
Month05 = sum(case month(a.CreateDate) when 5 then b.Amt else 0 end),
Month06 = sum(case month(a.CreateDate) when 6 then b.Amt else 0 end),
Month07 = sum(case month(a.CreateDate) when 7 then b.Amt else 0 end),
Month08 = sum(case month(a.CreateDate) when 8 then b.Amt else 0 end),
Month09 = sum(case month(a.CreateDate) when 9 then b.Amt else 0 end),
Month10 = sum(case month(a.CreateDate) when 10 then b.Amt else 0 end),
Month11 = sum(case month(a.CreateDate) when 11 then b.Amt else 0 end),
Month12 = sum(case month(a.CreateDate) when 12 then b.Amt else 0 end),
Total = sum(b.Amt)
from
Master a
inner join
Detail b
on
a.BillNo = b.BillNo
group by
a.CustomerID
2、
select
b.ItemID,
Month01 = sum(case month(a.CreateDate) when 1 then b.Amt else 0 end),
Month02 = sum(case month(a.CreateDate) when 2 then b.Amt else 0 end),
Month03 = sum(case month(a.CreateDate) when 3 then b.Amt else 0 end),
Month04 = sum(case month(a.CreateDate) when 4 then b.Amt else 0 end),
Month05 = sum(case month(a.CreateDate) when 5 then b.Amt else 0 end),
Month06 = sum(case month(a.CreateDate) when 6 then b.Amt else 0 end),
Month07 = sum(case month(a.CreateDate) when 7 then b.Amt else 0 end),
Month08 = sum(case month(a.CreateDate) when 8 then b.Amt else 0 end),
Month09 = sum(case month(a.CreateDate) when 9 then b.Amt else 0 end),
Month10 = sum(case month(a.CreateDate) when 10 then b.Amt else 0 end),
Month11 = sum(case month(a.CreateDate) when 11 then b.Amt else 0 end),
Month12 = sum(case month(a.CreateDate) when 12 then b.Amt else 0 end),
Total = sum(b.Amt)
from
Master a
inner join
Detail b
on
a.BillNo = b.BillNo
group by
b.ItemID
select
a.CustomerID,
Month01 = sum(case month(a.CreateDate) when 1 then b.Amt else 0 end),
Month02 = sum(case month(a.CreateDate) when 2 then b.Amt else 0 end),
Month03 = sum(case month(a.CreateDate) when 3 then b.Amt else 0 end),
Month04 = sum(case month(a.CreateDate) when 4 then b.Amt else 0 end),
Month05 = sum(case month(a.CreateDate) when 5 then b.Amt else 0 end),
Month06 = sum(case month(a.CreateDate) when 6 then b.Amt else 0 end),
Month07 = sum(case month(a.CreateDate) when 7 then b.Amt else 0 end),
Month08 = sum(case month(a.CreateDate) when 8 then b.Amt else 0 end),
Month09 = sum(case month(a.CreateDate) when 9 then b.Amt else 0 end),
Month10 = sum(case month(a.CreateDate) when 10 then b.Amt else 0 end),
Month11 = sum(case month(a.CreateDate) when 11 then b.Amt else 0 end),
Month12 = sum(case month(a.CreateDate) when 12 then b.Amt else 0 end),
Total = sum(b.Amt)
from
Master a
inner join
Detail b
on
a.BillNo = b.BillNo
group by
a.CustomerID
2、
select
b.ItemID,
Month01 = sum(case month(a.CreateDate) when 1 then b.Amt else 0 end),
Month02 = sum(case month(a.CreateDate) when 2 then b.Amt else 0 end),
Month03 = sum(case month(a.CreateDate) when 3 then b.Amt else 0 end),
Month04 = sum(case month(a.CreateDate) when 4 then b.Amt else 0 end),
Month05 = sum(case month(a.CreateDate) when 5 then b.Amt else 0 end),
Month06 = sum(case month(a.CreateDate) when 6 then b.Amt else 0 end),
Month07 = sum(case month(a.CreateDate) when 7 then b.Amt else 0 end),
Month08 = sum(case month(a.CreateDate) when 8 then b.Amt else 0 end),
Month09 = sum(case month(a.CreateDate) when 9 then b.Amt else 0 end),
Month10 = sum(case month(a.CreateDate) when 10 then b.Amt else 0 end),
Month11 = sum(case month(a.CreateDate) when 11 then b.Amt else 0 end),
Month12 = sum(case month(a.CreateDate) when 12 then b.Amt else 0 end),
Total = sum(b.Amt)
from
Master a
inner join
Detail b
on
a.BillNo = b.BillNo
group by
b.ItemID
解决方案 »
- 求一条SQL语句
- 数据库里面有个字段value的值为aa bb cc我怎么样才可以把他读出来,并且之间的空格也读出来!!
- SQL分组查询的问题
- 如何把这两个SQL合为一个,他们有一个相关联的ID,内联好像不行?
- master库是否可以隐藏或者怎么处理一下让别人在企业管理器中看不到,请高手帮忙给解释一下!!!!
- sql2005建立的数据库,可以导入sql2000去用吗?
- 简单问题!!!!
- 关于查询用户年龄的问题
- 存仓表的问题?50分以表谢意!万分感谢!
- 我在查询分析器中用 delete from table1将表中的所有数据都删除了,我该如何才能再恢复过来???
- 帮帮我,简单的多表查询
- 新手求助:这个作业怎么写?
CustomerID Month01 Month02 Month03……Month12,Total
1 … … … … …
2 … … … … …
----------------------------------------------------------------------------
declare @sql varchar(8000)
set @sql=''
select @sql=@sql+',month'+datename(month,a.CreateDate)+'=sum(case datename(month,a.CreateDate) when '+datename(month,a.CreateDate)
+ 'then b.amt else 0 end) from master a'
set @sql='select a.customerid '+@sql+ ', total=sum(amt) from
master a,detail b where a.billno=b.billno group by a.customerid'
exec(@sql)
二,统计ItemID的年度报表
ItemID Month01 Month02 Month03……Month12,Total
1 … … … … …
2 … … … … …要求上述两个统计报表能在最短的时间内统计出来,请高手给出合理的统计,谢谢哈
----------------------------------------------------------------------------
declare @sql varchar(8000)
set @sql=''
select @sql=@sql+',month'+datename(month,a.CreateDate)+'=sum(case datename(month,a.CreateDate) when '+datename(month,a.CreateDate)
+ 'then b.amt else 0 end) from master a'
set @sql='select b.itemid '+@sql+ ', total=sum(amt) from
master a,detail b where a.billno=b.billno group by b.itemid'
exec(@sql)
你的方法我测试了,30-50万条数据范围内统计Customer年度数据,需要15秒(个人PC),统计Item年度数据需要30秒