然后汇总相同kcode 中的 new_money 和 kmoney
以 kmoney 进行降序排列
并统计出kmoney 和 new_money :select kcode,sum(kmoney) as kmoney, sum(case when ktype ='yes' then kmoney *
konum when ktype = 'no' or ktype ='wait' then kmoney ) as new_money
from 你的表
group by kcode
order by kmoney desc
以 kmoney 进行降序排列
并统计出kmoney 和 new_money :select kcode,sum(kmoney) as kmoney, sum(case when ktype ='yes' then kmoney *
konum when ktype = 'no' or ktype ='wait' then kmoney ) as new_money
from 你的表
group by kcode
order by kmoney desc
from
(select kcode,sum(kmoney) as kmoney, sum(case when ktype ='yes' then kmoney *
konum when ktype = 'no' or ktype ='wait' then kmoney ) as new_money
from 你的表
group by kcode
) a
1.
select kcode,sum(kmoney) as kmoney, sum(case when ktype ='yes' then kmoney *
konum when ktype = 'no' or ktype ='wait' then kmoney end) as new_money
from 你的表
group by kcode
order by kmoney desc2.
select sum(kmoney) as total_kmoney, sum(new_money) as total_new_money
from
(select kcode,sum(kmoney) as kmoney, sum(case when ktype ='yes' then kmoney * konum when ktype = 'no' or ktype ='wait' then kmoney end ) as new_money
from 你的表
group by kcode
) a
,kmoney=sum(kmoney)
,new_money=sum(case when ktype='yes' then kmoney * konum
when ktype in('no','wait') then kmoney
else 0 end)
from 表
group by kcode with rollup
order by grouping(kcode),kmoney desc
create table 表(kcode char(4),kmoney int,ktype varchar(10),konum decimal(10,2))
insert 表 select '0001',900, 'wait',0.85
union all select '0001',3000,'yes' ,0.75
union all select '0001',1500,'yes' ,0.8
union all select '0002',900, 'no' ,0.8
union all select '0002',5000,'yes' ,0.9
union all select '0003',6200,'no' ,0.9
union all select '0003',850, 'wait',0.9
go--查询
select kcode=case grouping(kcode) when 1 then '合计' else kcode end
,kmoney=sum(kmoney)
,new_money=sum(case when ktype='yes' then kmoney * konum
when ktype in('no','wait') then kmoney
else 0 end)
from 表
group by kcode with rollup
order by grouping(kcode),kmoney desc
go--删除测试
drop table 表/*--测试结果kcode kmoney new_money
----- ----------- ----------------------------------------
0003 7050 7050.00
0002 5900 5400.00
0001 5400 4350.00
合计 18350 16800.00(所影响的行数为 4 行)
--*/
zjcxc( 邹建 :) 的方法也够牛的!
给分!呵呵...