比如说table a如下
id type num
1 A 1
2 B 2
3 C 5
4 A 3
5 C 2其中type的值可为{A,B,C,D}
我现在想对table a进行分组统计,如下
type sum
A 4
D 0
B 2
C 7
合计 13sql语句应该怎么写呢,各位帮帮忙
id type num
1 A 1
2 B 2
3 C 5
4 A 3
5 C 2其中type的值可为{A,B,C,D}
我现在想对table a进行分组统计,如下
type sum
A 4
D 0
B 2
C 7
合计 13sql语句应该怎么写呢,各位帮帮忙
union all
select '合计' type,sum(num) from
(select sum(num) num from temp group by type)
sum(num) as sum
from a
group by rollup(type)我这样写只能实现
type sum
A 4
B 2
C 7
total 13我想问问有什么方法可以让记录按自己的意愿排序,例如
type sum
D 0
A 4
C 7
B 2
total 13例外,如果数据库中没有一条记录中的type为D,则加入一条记录D,0。请问SQL该怎么写呢?
我对SQL不大熟悉,各位帮帮忙
select zz.ty as type, sum(zz.nu) num
from (select tt.type ty, nvl(a.num, 0) nu
from A a,
(select 'A' as type
from dual
union
select 'D' as type
from dual
union
select 'B' as type
from dual
union
select 'C' as type from dual) tt
where a.type = tt.type(+)) zz
group by zz.ty
union
select '合计' a.type, sum(a.num) num
from (select sum(a.num) from A a group by a.type);