交叉表合计结果部分有误
执行下列交叉表语句，能正常运行，就是部分数据未符合要求，请大大们斧正
--> --> (Roy)生成測試數據
set nocount on;
if not object_id('Tempdb..#T') is null
drop table #T
Go
Create table #T([XRQ] Datetime,[RYU] decimal(18,1),[RGL] int,[JCMC] nvarchar(5))
Insert #T
select '2012-04-03',8072.0,25522,N'_1分公司' union all
select '2012-04-02',8380.2,26913,N'_2分公司' union all
select '2012-04-01',8451.6,26572,N'_1分公司' union all
select '2012-04-03',8262.2,26527,N'_2分公司' union all
select '2012-04-02',8265.7,26048,N'_1分公司' union all
select '2012-04-01',8378.7,26896,N'_2分公司'
Go
declare @str varchar(8000)
set @str=''
declare @sql varchar(8000)
set @sql=''
select @str=@str+','+quotename([JCMC]+'油耗')+'=max(case when [JCMC]='+QUOTENAME([JCMC],'''')+' then [RYU] else 0 end)'+','
+quotename([JCMC]+'公里')+'=max(case when [JCMC]='+QUOTENAME([JCMC],'''')+' then [RGL] else 0 end)'+','
+quotename([JCMC]+'单耗')+'=max(case when [JCMC]='+QUOTENAME([JCMC],'''')+' then cast([RYU]*100.0/[RGL] as decimal(18,2)) else 0 end)'
from #T group by [JCMC]
select @sql=@sql+','+quotename([JCMC]+'油耗')+'=sum(case when [JCMC]='+QUOTENAME([JCMC],'''')+' then [RYU] else 0 end)'+','
+quotename([JCMC]+'公里')+'=sum(case when [JCMC]='+QUOTENAME([JCMC],'''')+' then [RGL] else 0 end)'+','
+quotename([JCMC]+'单耗')+'=max(case when [JCMC]='+QUOTENAME([JCMC],'''')+' then cast([RYU]*100.0/[RGL] as decimal(18,2)) else 0 end)'
from #T group by [JCMC]
--print @str
exec('select convert(varchar(10),[XRQ],120) as [XRQ]'+@str+
',sum([RYU]) as 横向油耗合计 ,sum([RGL]) as 横向公里合计,CAST(sum([RYU])/sum([RGL])*100 as decimal(18,2)) as 单耗 from #T group by [XRQ] union all
select ''竖向合计'''+@sql+',sum([RYU]),sum([RGL]),CAST((sum([RYU])/sum([RGL])*100) as decimal(18,2)) from #T ')
得到结果如下：
2012-04-018451.62657231.818378.72689631.1516830.35346831.48
2012-04-028265.72604831.738380.22691331.1416645.95296131.43
2012-04-038072.02552231.638262.22652731.1516334.25204931.38
竖向合计 24789.37814231.8125021.18033631.1549810.415847831.43
竖向合计中的百分比：31.81 31.15 是该列中的最大值
我要的是 24789.3/78142*100=的值和25021.1/80336*100=的值
其他计算结果正确
[解决办法]
把max改为sum
