Oracle 技能强化 Part 13 应用案例的分析学习

1.提取字符串中的元素。

造出测试数据，并加以测试：

CREATE OR REPLACE VIEW v AS
SELECT 'xxxxxabc[867]xxx[-]xxxx[5309]xxxxx' msg FROM dual UNION ALL
SELECT 'xxxxxtime:[11271978]favnum:[4]id:[Joe]xxxxx' msg FROM dual UNION ALL
SELECT 'call:[F_GET_ROWS()]b1:[ROSEWOOD…SIR]b2:[44400002]77.90xxxxx' msg FROM dual UNION ALL
SELECT 'film:[non_marked]qq:[unit]tailpipe:[withabanana?]80sxxxxx' msg FROM dual;

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;

2.检索出数字与字母的组合字符串。

测试数据如下：

CREATE OR REPLACE VIEW v AS
SELECT 'ClassSummary' strings FROM dual UNION ALL
SELECT '3453430278' FROM dual UNION ALL
SELECT 'findRow 55' FROM dual UNION ALL
SELECT '1010 switch' FROM dual UNION ALL
SELECT '333' FROM dual UNION ALL
SELECT 'threes' FROM dual;

SELECT strings
  FROM v
 WHERE regexp_like(v.strings, '([[:alpha:]].*[0-9]|[0-9].*[[:alpha:]])');

3.对结果进行分级并进行行转列操作

/*该问题解决思路如下
第一步、要先取对数据进行分档排序
第二步、要对三档数据重新生成序号
第三补、以第二步生成的需要为基准进行分组汇总*/

/*第一步、对数据分档*/
WITH x AS
(SELECT ename AS 姓名,
sal AS 工资,
dense_rank() over(ORDER BY sal DESC) AS 档次
FROM emp),
/*第二步、根据档次把数据分为三列*/
y AS
(SELECT 姓名, 工资, 档次,
CASE WHEN 档次 <= 3 THEN 1 WHEN 档次 <= 6 THEN 2 ELSE 3 END 列
FROM x),
/*第三步、分别对三列的数据重新取序号，这样相同序号的可以汇总后放在同一行*/
z AS
(SELECT 姓名, 工资, 档次, 列,
row_number() over(PARTITION BY 列 ORDER BY 档次, 姓名) AS 分组依据
FROM y)
/*数据整理完毕，汇总即可*/
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 分组依据

4.给两次转置的结果集增加列头。

测试数据如下：

CREATE TABLE IT_research (deptno number, ename varchar2(20));
INSERT INTO it_research VALUES (100, 'HOPKINS');
INSERT INTO it_research VALUES (100,'JONES');
INSERT INTO it_research VALUES (100,'TONEY');
INSERT INTO it_research VALUES (200,'MORALES');
INSERT INTO it_research VALUES (200,'P.WHITAKER');
INSERT INTO it_research VALUES (200,'MARCIANO');
INSERT INTO it_research VALUES (200,'ROBINSON');
INSERT INTO it_research VALUES (300,'LACY');
INSERT INTO it_research VALUES (300,'WRIGHT');
INSERT INTO it_research VALUES (300,'J.TAYLOR');

CREATE TABLE IT_apps (deptno number, ename varchar2(20));
INSERT INTO it_apps VALUES (400, 'CORRALES');
INSERT INTO it_apps VALUES (400,'MAYWEATHER');
INSERT INTO it_apps VALUES (400,'CASTILLO');
INSERT INTO it_apps VALUES (400,'MARQUEZ');
INSERT INTO it_apps VALUES (400,'MOSLEY');
INSERT INTO it_apps VALUES (500,'GATTI');
INSERT INTO it_apps VALUES (500,'CALZAGHE');
INSERT INTO it_apps VALUES (600,'LAMOTTA');
INSERT INTO it_apps VALUES (600,'HAGLER');
INSERT INTO it_apps VALUES (600,'HEARNS');
INSERT INTO it_apps VALUES (600,'FRAZIER');
INSERT INTO it_apps VALUES (700,'GUINN');
INSERT INTO it_apps VALUES (700,'JUDAH');
INSERT INTO it_apps VALUES (700,'MARGARITO');

需求是:将以上两个表中的数据转为一列显示。

解决方案：构建两个表的JOIN列，此处用行号进行关联。

SELECT a.research, b.apps
  FROM (SELECT
        /*按预定顺序生成对应行号*/
         row_number() over(ORDER BY deptno, ename NULLS FIRST) AS sn,
         coalesce(ename, to_char(deptno)) AS research
          FROM (
                /*先把表转为一列*/
                SELECT deptno, NULL AS ename
                  FROM it_research
                 GROUP BY deptno
                UNION ALL
                SELECT deptno, ' ' || ename AS ename
                  FROM it_research)) a
  FULL OUTER JOIN (SELECT
                   /*按预定顺序生成对应行号*/
                    row_number() over(ORDER BY deptno, ename NULLS FIRST) AS sn,
                    coalesce(ename, to_char(deptno)) AS apps
                     FROM (
                           /*先把表转为一列*/
                           SELECT deptno, NULL AS ename
                             FROM it_apps
                            GROUP BY deptno
                           UNION ALL
                           SELECT deptno, ' ' || ename AS ename
                             FROM it_apps)) b
/*有了行号后一切都是那样的简单*/
    ON b.sn = a.sn;

 5.对字符串进行多列拆分

测试数据如下：

CREATE OR REPLACE VIEW V AS
SELECT 'entry:stewiegriffin:lois:brian:' STRINGS FROM dual UNION ALL
SELECT 'entry:moe::sizlack:' FROM dual UNION ALL
SELECT 'entry:petergriffin:meg:chris:' FROM dual UNION ALL
SELECT 'entry:willie:' FROM dual UNION ALL
SELECT 'entry:quagmire:mayorwest:cleveland:' FROM dual UNION ALL
SELECT 'entry:::flanders:' FROM dual UNION ALL
SELECT 'entry:robo:tchi:ken:' FROM DUAL;

SELECT v.strings,
rtrim(regexp_substr(v.strings, '([^:]*:)', 1, 2), ':') AS 列1,
rtrim(regexp_substr(v.strings, '([^:]*:)', 1, 3), ':') AS 列2,
rtrim(regexp_substr(v.strings, '([^:]*:)', 1, 4), ':') AS 列3
FROM v
ORDER BY 1;

6.测试某个特定值是否在某个组内。

某个学生三个月内参加了三次考试，如果通过了其中一次考试，就满足了要求，此时返回“+”，否则返回“-”。且没有通过的科目最后一次要增 加标识 1，其它行标识为 0。

CREATE OR REPLACE VIEW v AS
SELECT 1 student_id, 1 test_id, 2 grade_id, 1 period_id, to_date('02012005', 'MMDDYYYY') test_date, 0 ps_fail
FROM dual
UNION ALL
SELECT 1 student_id, 2 test_id, 2 grade_id, 1 period_id, to_date('03012005', 'MMDDYYYY') test_date, 1 ps_fail
FROM dual
UNION ALL
SELECT 1 student_id, 3 test_id, 2 grade_id, 1 period_id, to_date('04012005', 'MMDDYYYY') test_date, 0 ps_fail
FROM dual
UNION ALL
SELECT 1 student_id, 4 test_id, 2 grade_id, 2 period_id, to_date('05012005', 'MMDDYYYY') test_date, 0 ps_fail
FROM dual
UNION ALL
SELECT 1 student_id, 5 test_id, 2 grade_id, 2 period_id, to_date('06012005', 'MMDDYYYY') test_date, 0 ps_fail
FROM dual
UNION ALL
SELECT 1 student_id, 6 test_id, 2 grade_id, 2 period_id, to_date('07012005', 'MMDDYYYY') test_date, 0 ps_fail
FROM dual;

SELECT test_id,
       student_id,
       grade_id,
       period_id,
       ps_fail AS 是否通过,
       test_date AS 测试日期,
       decode(当期是否通过, 1, lpad('+', 6), lpad('-', 6)) AS 状态,
       decode(当期是否通过, 1, 0, decode(test_date, 最后测试日期, 1, 0)) AS 测试中
  FROM (SELECT v.*,
               MAX(ps_fail) over(PARTITION BY student_id, grade_id, period_id) AS 当期是否通过,
               MAX(test_date) over(PARTITION BY student_id, grade_id, period_id) AS 最后测试日期
          FROM v) x