一.CASE WHEN THEN ELSE END条件控制查询
1.简单CASE函数用法
SELECT ID,STU_NAME CASE WHEN SCORE >=90 THEN '优秀' WHEN SCORE>=75 AND SCORE<90 THEN '良好' WHEN SCORE>=60 AND SCORE<75 THEN '及格' ELSE '不及格' END RESULT FROM STUDENT;
SELECT GRADE,COUNT(CASE WHEN SEX = 1 THEN 1 ELSE NULL END) 男生数, COUNT(CASE WHEN SEX = 2 THEN 1 ELSE NULL END) 女生数 FROM STUDENT GROUP BY GRADE;
2.CASE搜索函数
SELECT T2.*,T1.* FROM T1,T2 WHERE (CASE WHEN T2.TYPE = 'A' AND T1.NAME LIKE '%海哥' THEN 1 WHEN T2.TYPE <> 'A' AND T1.NAME NOT LIKE '%海哥' THEN 1 ELSE 0 END) = 1
SELECT CASE WHEN SALARY <=2000 THEN '低收入' WHEN SALARY >2000 AND SALARY<=4000 THEN '温饱收入' WHEN SALARY >4000 AND SALARY <8000 THEN '小康收入' WHEN SALARY >8000 AND SALARY <12000 THEN '中产收入' ELSE '高等收入' END SALARY_LEVEL, COUNT(1) FROM EMPLOYEE GROUP BY CASE WHEN SALARY <=2000 THEN '低收入' WHEN SALARY >2000 AND SALARY<=4000 THEN '温饱收入' WHEN SALARY >4000 AND SALARY <8000 THEN '小康收入' WHEN SALARY >8000 AND SALARY <12000 THEN '中产收入' ELSE '高等收入' END;
需要注意的问题,Case函数只返回第一个符合条件的值,剩下的Case部分将会被自动忽略
二.递归查询
1.查询ID=20178900的所有直属子节点,所有后代(PRIOR 后接子节点,START WITH 开始的地方就是根节点)
SELECT A.ID,A.MC,A.CODE FROM VILLAGE A WHERE A.CODE IN (SELECT ID FROM CITY S START WITH ID='20178900' CONNECT BY S.CODE = PRIOR S.ID) AND A.STATUS = '1' AND MC LIKE '%朱家庄%' ORDER BY A.TIME DESC ,A.ID DESC;
2.查询ID=20178900的所有直属父节点,所有祖宗(PRIOR 后接父节点,START WITH 开始的地方就是根节点)
SELECT A.ID,A.MC,A.CODE FROM VILLAGE A WHERE A.CODE IN (SELECT ID FROM CITY S START WITH ID='20178900' CONNECT BY PRIOR S.CODE = S.ID) AND A.STATUS = '1' AND MC LIKE '%朱家庄%' ORDER BY A.TIME DESC ,A.ID DESC;
总结:这两条语句之间的区别在于PRIOR关键字的位置不同,所以决定了查询的方式不同.当Parent = PRIOR ID时,数据库会根据当前的id迭代出Parent与该id相同的记录,所以查询的结果是迭代出了所有的子类记录;而PRIOR Parent = id时,数据库会根据当前的Parent来迭代出与当前的Parent相同的id的记录,所以查询出来的结果就是所有的父类结果.