Oracle SQL精妙SQL语句讲解
--行列转换 行转列
SELECT CASEWHEN a.deptno IS NULL THEN'合计'WHEN a.deptno IS NOT NULL AND a.empno IS NULL THEN'小计'ELSE'' || a.deptnoEND deptno,a.empno,a.ename,SUM(a.sal) total_salFROM scott.emp aGROUP BY GROUPING SETS((a.deptno),(a.deptno, a.empno, a.ename),());-- 分组排序SELECT a.deptno,a.empno,a.ename,a.sal,-- 可跳跃的rankrank() over(PARTITION BY a.deptno ORDER BY a.sal DESC) r1,-- 密集型rankdense_rank() over(PARTITION BY a.deptno ORDER BY a.sal DESC) r2,-- 不分组排序rank() over(ORDER BY sal DESC) r3FROM scott.emp aORDER BY a.deptno,a.sal DESC;-- 当前行数据和前/后n行的数据比较SELECT a.empno,a.ename,a.sal,-- 上面一行lag(a.sal) over(ORDER BY a.sal DESC) lag_1,-- 下面三行lead(a.sal, 3) over(ORDER BY a.sal DESC) lead_3FROM scott.emp aORDER BY a.sal DESC;
