ORACLE--SQL日常问题和技巧2（自定义排序，递归查询，异常ORA-01747，逗号隔开的字符串转成in条件，用符号连接表中某字段）

1.有些情况需要将几条记录按要求排序，适用于少量要求

表如图所示：

按照e，u，r，o，t，w，q，y，i顺序排序：

SELECT
    * 
FROM
    LGQ_TEST 
ORDER BY
    (
    CASE
            
            WHEN s = 'e' THEN
            1 
            WHEN S = 'u' THEN
            2 
            WHEN S = 'r' THEN
            3 
            WHEN S = 'o' THEN
            4 
            WHEN S = 't' THEN
            5 
            WHEN S = 'W' THEN
            6 
            WHEN S = 'q' THEN
            7 
            WHEN S = 'y' THEN
            8 
            WHEN S = 'i' THEN
            9 ELSE 0 
    END 
    )

 2.递归查询（递归子孙和递归祖先）

-- 递归子孙
SELECT
    pur.CODE 
FROM
    PUB_REGION pur START WITH pur.CODE = '370000000000' CONNECT BY PRIOR pur.CODE = pur.PARENT_CODE
    
-- 递归祖先
    SELECT
    pur.CODE 
FROM
    PUB_REGION pur START WITH pur.CODE = '370100000000' CONNECT BY PRIOR pur.PARENT_CODE=pur.CODE

 3.异常：ORA-01747: user.table.column, table.column 或列说明无效

因为用了oracle关键字

4.将用逗号隔开的字符串转成in条件

SELECT
    regexp_substr( TO_CHAR( 'id1,id2,id3' ), '[^,]+', 1, LEVEL ) 
FROM
    dual CONNECT BY regexp_substr( TO_CHAR( 'id1,id2,id3' ), '[^,]+', 1, LEVEL ) IS NOT NULL

结果为：

--使用
SELECT
    * 
FROM
TABLE 
WHERE
    TABLE.ID IN (
    SELECT
        regexp_substr( TO_CHAR( 'id1,id2,id3' ), '[^,]+', 1, LEVEL ) 
FROM
    dual CONNECT BY regexp_substr( TO_CHAR( 'id1,id2,id3' ), '[^,]+', 1, LEVEL ) IS NOT NULL)

 5.用自定义符号连接表中某字段，函数LISTAGG()

这个例子是吧这个表中所有记录的id用--连接起来

SELECT
    LISTAGG(PI.ID,'--') WITHIN GROUP(ORDER BY PI.ID DESC) AS STR
FROM
    PROJECT_INFO PI 
WHERE
    1=1

结果为：