SELECT decode(GROUPING(dname), 1, '合计', dname) dname, job, SUM(maxsal) FROM (SELECT a.dname, b.job, maxsal FROM dept a, (SELECT t.deptno, job, MAX(sal) maxsal FROM emp t GROUP BY t.deptno, job) b WHERE a.deptno = b.deptno) GROUP BY ROLLUP((dname, job)); --或者 WITH b AS (SELECT t.deptno, job, MAX(sal) maxsal FROM emp t GROUP BY t.deptno, job) SELECT a.dname, b.job, maxsal FROM dept a, b WHERE a.deptno = b.deptno UNION ALL SELECT '合计', NULL, SUM(maxsal) FROM b;
FROM (SELECT a.dname, b.job, maxsal
FROM dept a, (SELECT t.deptno, job, MAX(sal) maxsal FROM emp t GROUP BY t.deptno, job) b
WHERE a.deptno = b.deptno)
GROUP BY ROLLUP((dname, job));
--或者
WITH b AS
(SELECT t.deptno, job, MAX(sal) maxsal FROM emp t GROUP BY t.deptno, job)
SELECT a.dname, b.job, maxsal
FROM dept a, b
WHERE a.deptno = b.deptno
UNION ALL
SELECT '合计', NULL, SUM(maxsal) FROM b;