用group by 扩展函数实现sqlplus的break功能

---sqlplus的break

SELECT dname,
      lag(a.dname,1)over(PARTITION BY a.dname ORDER BY a.dname,b.job) ,
      b.job,SUM(b.sal) sum_sal
FROM dept a,emp b
WHERE a.deptno = b.deptno
GROUP BY ROLLUP(a.dname,b.job)
ORDER BY 1,3

----结合decode函数进行转换

SELECT decode(lag(a.dname,1)over(PARTITION BY a.dname ORDER BY a.dname,b.job),a.dname,null,a.dname) dname,
       b.job,SUM(b.sal) sum_sal
FROM dept a,emp b
WHERE a.deptno = b.deptno
GROUP BY ROLLUP(a.dname,b.job)
ORDER BY a.dname,b.job