SQL日常问题和技巧2（自定义排序，异常ORA-01747，逗号隔开的字符串转成in条件，持续记录）...

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

表如图所示：

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

 1 SELECT
 2     * 
 3 FROM
 4     LGQ_TEST 
 5 ORDER BY
 6     (
 7     CASE
 8             
 9             WHEN s = 'e' THEN
10             1 
11             WHEN S = 'u' THEN
12             2 
13             WHEN S = 'r' THEN
14             3 
15             WHEN S = 'o' THEN
16             4 
17             WHEN S = 't' THEN
18             5 
19             WHEN S = 'W' THEN
20             6 
21             WHEN S = 'q' THEN
22             7 
23             WHEN S = 'y' THEN
24             8 
25             WHEN S = 'i' THEN
26             9 ELSE 0 
27     END 
28     )

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

 1 -- 递归子孙
 2 SELECT
 3     pur.CODE 
 4 FROM
 5     PUB_REGION pur START WITH pur.CODE = '370000000000' CONNECT BY PRIOR pur.CODE = pur.PARENT_CODE
 6     
 7 -- 递归祖先
 8     SELECT
 9     pur.CODE 
10 FROM
11     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

结果为：

 1 --使用
 2 SELECT
 3     * 
 4 FROM
 5 TABLE 
 6 WHERE
 7     TABLE.ID IN (
 8     SELECT
 9         regexp_substr( TO_CHAR( 'id1,id2,id3' ), '[^,]+', 1, LEVEL ) 
10 FROM
11     dual CONNECT BY regexp_substr( TO_CHAR( 'id1,id2,id3' ), '[^,]+', 1, LEVEL ) IS NOT NULL)

 

转载于:https://www.cnblogs.com/lgqrlchinese/p/11381766.html