原始数据
prov city cnt
江苏 南京 1
江苏 苏州 1
江苏 无锡 1
浙江 杭州 1
浙江 温州 1我想按省分别小计一下,得到如下数据
江苏 南京 1
江苏 苏州 1
江苏 无锡 1
江苏 小计 3
浙江 杭州 1
浙江 温州 1
浙江 小计 2请教大家
prov city cnt
江苏 南京 1
江苏 苏州 1
江苏 无锡 1
浙江 杭州 1
浙江 温州 1我想按省分别小计一下,得到如下数据
江苏 南京 1
江苏 苏州 1
江苏 无锡 1
江苏 小计 3
浙江 杭州 1
浙江 温州 1
浙江 小计 2请教大家
select '江苏' prov,'南京' city,1 cnt from dual
union all
select '江苏' prov,'苏州' city,1 cnt from dual
union all
select '江苏' prov,'无锡' city,1 cnt from dual
union all
select '浙江' prov,'杭州' city,1 cnt from dual
union all
select '浙江' prov,'温州' city,1 cnt from dual
)
select * from
(select prov,'小计' city,sum(cnt) cnt from tmp group by prov
union
select prov,city,cnt from tmp)
order by prov,cnt------------------
1 江苏 南京 1
2 江苏 苏州 1
3 江苏 无锡 1
4 江苏 小计 3
5 浙江 杭州 1
6 浙江 温州 1
7 浙江 小计 2
SQL> edi
已写入 file afiedt.buf 1 with tb as
2 ( select '江苏' prov,'南京' city, 1 cnt from dual
3 union all
4 select '江苏' prov,'苏州' city,1 cnt from dual
5 union all
6 select '江苏' prov,'无锡' city,1 cnt from dual
7 union all
8 select '浙江' prov,'杭州' city,1 cnt from dual
9 union all
10 select '浙江' prov,'温州' city,1 cnt from dual
11 )
12 select prov,decode(grouping(city),1,'小计',city),sum(cnt)
13 from tb
14 group by rollup(prov,city)
15* having grouping(prov)=0
SQL> /PROV DECO SUM(CNT)
---- ---- ----------
江苏 南京 1
江苏 苏州 1
江苏 无锡 1
江苏 小计 3
浙江 杭州 1
浙江 温州 1
浙江 小计 2已选择7行。
(select prov,'小计' city,sum(cnt) cnt from 表名 group by prov
union
select prov,city,cnt from 表名)
order by prov,cnt
select '江苏' prov, '南京' city, 1 cnt from dual union all
select '江苏' prov, '苏州' city, 1 cnt from dual union all
select '江苏' prov, '无锡' city, 1 cnt from dual union all
select '浙江' prov, '杭州' city, 1 cnt from dual union all
select '浙江' prov, '温州' city, 1 cnt from dual)
SELECT t.prov, decode(grouping_id(t.city), 1, '小计', city), COUNT(1)
FROM test t
GROUP BY GROUPING SETS((t.prov, t.city),(t.prov));
select prov,city,cnt from test
union all
select prov,'total',sum(cnt) from test group by prov order by 1,3;
SELECT * FROM
(
SELECT prov,city,cnt FROM TB_原始数据
UNION ALL
SELECT PROV,'小计' AS CNT,SUM(CNT) FROM TB__原始数据 GROUP BY PROV
) ORDER BY PROV,CNT;
with test as (
select '江苏' prov,'南京' city, 1 cnt from dual union all
select '江苏' prov,'苏州' city, 1 cnt from dual union all
select '江苏' prov,'无锡' city, 1 cnt from dual union all
select '浙江' prov,'杭州' city, 1 cnt from dual union all
select '浙江' prov,'温州' city, 1 cnt from dual)
SELECT *
FROM (SELECT prov, city, cnt
FROM test
UNION ALL
SELECT prov, '小计', SUM(cnt) FROM test GROUP BY prov)
ORDER BY 1, 3;
--2.grouping sets
with test as (
select '江苏' prov,'南京' city, 1 cnt from dual union all
select '江苏' prov,'苏州' city, 1 cnt from dual union all
select '江苏' prov,'无锡' city, 1 cnt from dual union all
select '浙江' prov,'杭州' city, 1 cnt from dual union all
select '浙江' prov,'温州' city, 1 cnt from dual)
SELECT prov, decode(GROUPING(city), 1, '小计', city) city, SUM(cnt)
FROM test
GROUP BY GROUPING SETS((prov, city),(prov));
--3.rollup(多一个合计)
with test as (
select '江苏' prov,'南京' city, 1 cnt from dual union all
select '江苏' prov,'苏州' city, 1 cnt from dual union all
select '江苏' prov,'无锡' city, 1 cnt from dual union all
select '浙江' prov,'杭州' city, 1 cnt from dual union all
select '浙江' prov,'温州' city, 1 cnt from dual)
SELECT prov, decode(GROUPING_ID(prov, city), 1, '小计', 3, '合计', city) city, SUM(cnt)
FROM test
GROUP BY ROLLUP(prov, city);
CASE WHEN CITY IS NULL THEN '小计' ELSE CITY END CITY,
SUM(CNT) CNT
FROM TABLE_NAME
GROUP BY ROLLUP(PROV, CITY)
ORDER BY PROV, CITY
CASE WHEN CITY IS NULL THEN '小计' ELSE CITY END CITY,
SUM(CNT) CNT
FROM TABLE_NAME
GROUP BY ROLLUP(PROV, CITY)
ORDER BY PROV, CITY
CASE WHEN CITY IS NULL THEN '小计' ELSE CITY END CITY,
SUM(CNT) CNT
FROM TABLE_NAME
GROUP BY ROLLUP(PROV, CITY)
ORDER BY PROV, CITY