《SQL经典实例》：8. 变换展示方式，层次关系

目录

变换结果集为一行&多列

层次关系

---

变换结果集为一行&多列

• 想由左图变为右图这种展示方式：

                           

SELECT 
sum(CASE WHEN deptno = 10 THEN 1 ELSE 0 END) AS deptno_10,
sum(CASE WHEN deptno = 20 THEN 1 ELSE 0 END) AS deptno_20,
sum(CASE WHEN deptno = 30 THEN 1 ELSE 0 END) AS deptno_30
FROM emp;

• 想汇总成如下展示方式：

SELECT 
min(CASE WHEN job = 'clerk' THEN ename ELSE null END) AS 'clerks',
min(CASE WHEN job = 'analyst' THEN ename ELSE null END) AS 'analyst',
min(CASE WHEN job = 'manager' THEN ename ELSE null END) AS 'manager',
min(CASE WHEN job = 'president' THEN ename ELSE null END) AS 'president',
min(CASE WHEN job = 'salesman' THEN ename ELSE null END) AS 'salesman'
FROM
(SELECT e.job, e.ename, 
(SELECT count(*) FROM emp d WHERE d.job = e.job AND d.empno > e.empno) AS rnk
FROM emp e ORDER BY rnk)x
GROUP BY rnk;

这种变换的关键在于，给每个人赋予同一个job内部排名，这样才不会出现这种情况：

 

层次关系

确认叶子节点、分支节点和根节点，针对每一种节点类型分别计算出正确的“布尔”值。

想得到如下结果：

SELECT e.ename,
(SELECT SIGN(count(*)) FROM emp d WHERE (SELECT count(*) FROM emp f WHERE f.mgr = e.empno) = 0) AS is_leaf,
(SELECT SIGN(count(*)) FROM emp d WHERE d.mgr = e.empno AND e.mgr is not null) AS is_branch, 
(SELECT SIGN(count(*)) FROM emp d WHERE d.empno = e.empno AND d.mgr is null) AS is_root
FROM emp e
ORDER BY 4 desc, 3 desc, 2 desc;

利用SIGN函数，返回布尔值：当sign内是整数，返回1；是0，返回0，是负数，返回-1