《Oracle查询优化改写》笔记（应用案例）

发表于 | 分类于 | 本文总阅读量 次

《Oracle查询优化改写》笔记（基础语句）

《Oracle查询优化改写》笔记（高级查询）

提取不同位置字符串

1
2
3
4
5
6
SELECT 
    ltrim(regexp_substr(v.msg,'(\[)([^]]+)',1,1),'[') 第一个串,
    ltrim(regexp_substr(v.msg,'(\[)([^]]+)',1,2),'[') 第二个串,
    ltrim(regexp_substr(v.msg,'(\[)([^]]+)',1,3),'[') 第三个串
    msg
FROM v;

查询字母数字混合字符串

1
2
3
SELECT strings
FROM v
WHERE regexp_like(v.strings,'([a-zA-Z].*[0-9]|[0-9].*[a-zA-Z])');

结果分级并转为列

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
WITH
/* 1.对数据分档 */
x AS (
    SELECT ename AS 姓名,sal AS 工资,
        dense_rank() over(ORDER BY sal DESC) AS 档次
    FROM emp
),
/* 2.根据档次把数据分为三类 */
y AS (
    SELECT 姓名,工资,档次
        CASE WHEN 档次<=3 THEN 1 WHEN 档次<=6 THEN 2 ELSE 3 END 列
    FROM x
),
/* 3.分别对三列的数据重新取序号，相同序号的汇总后可以放在同一行 */
z AS (
    SELECT 姓名,工资,档次,列
        row_number() over(PARTITION BY 列 ORDER BY 档次,姓名) AS 分组依据
    FROM y
)

/* 4.行转列 */
SELECT 
    MAX(CASE 列 WHEN 1 THEN rpad(姓名,6) || ' (' || 工资 || ')' END) 最高三档,
    MAX(CASE 列 WHEN 2 THEN rpad(姓名,6) || ' (' || 工资 || ')' END) 次高三档,
    MAX(CASE 列 WHEN 3 THEN rpad(姓名,6) || ' (' || 工资 || ')' END) 其余档次
FROM z
GROUP BY 分组依据
/* 注意要排序，否则显示是乱的 */
ORDER BY 分组依据;

用隐藏数据进行行转列

要求每个job显示一行，同类job的姓名分列显示，不够的显示为空：

隐藏的条件是各员工姓名的顺序：

1
2
3
SELECT job, ename, 
    row_number() over(PARTITION BY job ORDER BY empno) AS sn
FROM emp;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
/* 方法一：CASE WHEN */
SELECT job,
    MAX(CASE WHEN sn=1 THEN ename END) AS n1,
    MAX(CASE WHEN sn=2 THEN ename END) AS n2,
    MAX(CASE WHEN sn=3 THEN ename END) AS n3,
    MAX(CASE WHEN sn=4 THEN ename END) AS n4
FROM (
    SELECT job, ename, 
        row_number() over(PARTITION BY job ORDER BY empno) AS sn
    FROM emp;
) GROUP BY job;

/* 方法二：行转列函数 */
SELECT * FROM (
    SELECT job, ename, 
        row_number() over(PARTITION BY job ORDER BY empno) AS sn
    FROM emp;
) PIVOT (
    MAX(ename) FOR sn IN (1 AS n1,2 AS n2,3 AS n3,4 AS n4)
);

若不知道有多少列，就需要先查询，再用循环语句拼装出需要的SQL：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
DECLARE
    V_MAX_SEQ NUMBER;
    V_SQL VARCHAR2(4000);
BEGIN
    SELECT MAX(COUNT(*)) INTO V_MAX_SEQ FROM EMP GROUP BY JOB;
    V_SQL := 'select' || CHR(10);
    FOR I IN 1 .. V_MAX_SEQ LOOP
        V_SQL := V_SQL || ' max(case when seq= ' || TO_CHAR(I) ||
        ' then ename end) as n' || TO_CHAR(I) || ',' || CHR(10);
    END LOOP;
    V_SQL := V_SQL || ' job from (
        select job, ename,
            row_number() over(PARTITION BY job ORDER BY empno) as seq
        from emp
    ) group by job';
    DBMS_OUTPUT.PUT_LINE(V_SQL);
END;
/* 
Chr函数 返回：返回 String，其中包含有与指定的字符代码相关的字符。
    Chr("7") 响铃
    Chr("8") 回格
    Chr("9") tab(水平制表符)
    Chr("10") 换行
    Chr("11") tab(垂直制表符)
    Chr("12") 换页
    Chr("13") 回车 chr(13)&chr(10) 回车和换行的组合
*/

正则提取CLOB里的文本

字段中内容为：

因为回车符不能用可见字符表示，所以使用 chr 函数 来把文本拆分为多行处理：

1
2
3
4
5
6
7
8
9
10
11
SELECT c1,
    regexp_substr(c1,'[^|#]+',1,1) AS d1,
    regexp_substr(c1,'[^|#]+',1,2) AS d2,
    regexp_substr(c1,'[^|#]+',1,3) AS d3,
    regexp_substr(c1,'[^|#]+',1,4) AS d4,
    regexp_substr(c1,'[^|#]+',1,5) AS d5
FROM (
    SELECT to_char(regexp_substr(c1,'[^' || CHR(10) || ']+',1,LEVEL+1)) AS c1
    FROM test 
    CONNECT BY LEVEL <= regexp_count(c1, chr(10))
);

（待续）

0%