SQL合计汇总实现数据N+1条显示:
注意group by rollup((ename, job, empno))!!!
select decode(grouping(ename) + grouping(job) + grouping(empno),
3,
'合计',
ename) ename,
decode(grouping(ename) + grouping(job) + grouping(empno),
3,
'-',
job) job,
decode(grouping(ename) + grouping(job) + grouping(empno),
3,
'-',
empno) empno,
sum(sal),
sum(comm)
from emp
group by rollup((ename, job, empno));
效果图:
SQL小计+合计实现,这个SQL小计的是每个job有多少数量:
select decode(grouping(job) + grouping(deptno), 1, '小计', 2, '总计', job) job,
sum(sal) sal,
sum(nvl(comm, 0)) comm,
decode(grouping(deptno), 1, count(*) || '条', deptno) dep