Oracle 两种高级查询总结

痛定思痛。 2022-05-31 02:28 189阅读 0赞

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