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

本文主要是介绍《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 

这篇关于《SQL经典实例》：8. 变换展示方式，层次关系的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！