Oracle两种高级查询总结（实例讲解）

一.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的记录,所以查询出来的结果就是所有的父类结果.