高手进，看看一条语句能否解决？
Type的取值
Typevalude
1A业务
2B业务
3C业务
表中的数据为：
nameCodeTypefee1fee2fee3
公司106766100063120.0030.0040.00
公司206066130.0020.0010.00
公司3040506210.0020.0030.00
公司409981210.0010.0010.00
公司5075523320.0020.0020.00
公司606766010170320.0020.0020.00
能否用一条SQL搞定？group by rollup or grouping
nameCodeTypefee1fee2fee3
公司106766100063120.0030.0040.00
公司206066130.0020.0010.00
A业务小计 小计50.0050.0050.00
公司3040506210.0020.0030.00
公司409981210.0010.0010.00
B业务小计20.0030.0040.00
公司5075523320.0020.0020.00
公司606766010170320.0020.0020.00
C业务小计 小计40.0040.0040.00
所有业务总计 总计110.00120.00130.00
[解决办法]
A业务小计1505050
公司1067661000631203040
公司2060661302010
B业务小计2203040
公司4099812101010
公司30405062102030
C业务小计3404040
公司50755233202020
公司6067660101703202020
所有业务总计总计110120130
[解决办法]
select * from (
select * from tb
union
select a.valude,b.* from
( select '小计 ' code,type,sum(fee1) fee1,sum(fee2) fee2,sum(fee3) fee3 from tb group by type ) b
left join ta a on a.type=b.type
Union
select '所有业务总计 ' name , '总计 ' code,null type,sum(fee1) fee1,sum(fee2) fee2,sum(fee3) fee3 from tb
) order by type ,fee1
[解决办法]
select nvl(name,decode(Type,1, 'A業務 ',2, 'B業務 ',3, 'C業務 ', '合計 ')),
decode(nvl(name,0),0,decode(nvl(Type,0),0, '合計 ', '小計 ',max(code)),
decode(nvl(name,0),0, ' ',to_char(Type)),
sum(fee1),sum(fee2),sum(fee3)
group by rollup(Type,name)
[解决办法]
sql server 下測試
==============================================
declare @tt table(name varchar(20),Code varchar(50),Type int,fee1 float,fee2 float,fee3 float)
insert into @tt
select '公司1 ', '06766100063 ',1,20.00,30.00,40.00 union all
select '公司2 ', '06066 ',1,30.00,20.00,10.00 union all
select '公司3 ', '040506 ',2,10.00,20.00,30.00 union all
select '公司4 ', '09981 ',2,10.00,10.00,10.00 union all
select '公司5 ', '075523 ',3,20.00,20.00,20.00 union all
select '公司6 ', '06766010170 ',3,20.00,20.00,20.00
select isnull(name,case when Type=1 then 'A ' when Type=2 then 'B ' when Type=3 then 'C ' else '合計 ' end),
case when isnull(name, ' ')= ' ' then
case when isnull(Type, ' ')= ' ' then '合計 ' else '小計 ' end else max(Code) end,
case when isnull(name, ' ')= ' ' then ' ' else cast(Type as varchar(2)) end ,sum(fee1),sum(fee2),sum(fee3)
from @tt
group by Type,[name] with rollup
==============================
公司106766100063120.030.040.0
公司206066130.020.010.0
A小計50.050.050.0
公司3040506210.020.030.0
公司409981210.010.010.0
B小計20.030.040.0
公司5075523320.020.020.0
公司606766010170320.020.020.0
C小計40.040.040.0
合計合計110.0120.0130.0
------解决方案--------------------
select decode(grouping_id(tb.type,ROWNUM),3, '总计 ',decode(GROUPING(ROWNUM), 1, max(ta.valude), MAX(tb.name))) name,
decode(grouping_id(tb.type,ROWNUM),3, '总计_code ',decode(GROUPING(ROWNUM), 1, '小计CODE ', MAX(tb.code))) code,
decode(GROUPING(ROWNUM), 1, NULL, MAX(tb.type)) type,
SUM(tb.fee1) fee1,SUM(tb.fee2) fee2,SUM(tb.fee3) fee3
FROM ta, tb
WHERE ta.type = tb.type
GROUP BY ROLLUP(tb.type, ROWNUM)
