目录
变换结果集为一行&多列
- 想由左图变为右图这种展示方式:
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