关于SQL统计问题
0
悬赏园豆：5
[待解决问题]
| 料号 | 序号 | 币制 | 品名 | 数量 |
| AA | 1 | USD | XXXX | 1 |
| TOTAl（根据币制） | 1 | |||
| AA | 1 | RMB | XXXX | 2 |
| TOTAL（序号） | 3 | |||
| BB | 2 | RMB | XXXX | 2 |
| TOTAl（根据币制） | 2 | |||
| TOTAL（序号） | 2 | |||
| TOTAL（合计） | 5 |
请教大神们，怎么写这个sql
描述：这张表，首先根据【币制】进行统计，然后在根据【序号】进行统计。最后整个数据合计。
请问这个sql怎么写！！
所有回答(2)
0
case when
0
可以分别产生按币种汇总的临时表,和按序号汇总的临时表,再和原表一起union
加以特别处理的排序规则即可
with cte_i as ( select 料号,序号,币制,1 as i_level,数量 from table ) ,cte_a as ( select 料号,序号,币制,2 as i_level,sum(数量) as 数量 from table group by 料号,序号,币制 ) ,cte_b as ( select 料号,序号,'序号total' as 币制,3 as i_level,sum(数量) as 数量 from table group by 料号,序号 ) ,cte_c as ( select '' as 料号,99999999 as 序号,'' as 币制,4 as i_level sum(数量) as 数量 ) select * from cte_i union all select * from cte_a union all select * from cte_b union all select * from cte_c order by case when 料号 = '' then 2 else 1 end, 料号, case when 序号 = '序号total' then 2 else 1 end, 序号,币制,i_level
