oracle sql解析详解,oracle sql精妙sql语句讲解_数据库技巧

--行列转换 行转列

DROP TABLE t_change_lc;

CREATE TABLE t_change_lc (card_code VARCHAR2(3), q NUMBER, bal NUMBER);

INSERT INTO t_change_lc

SELECT 001 card_code, ROWNUM q, trunc(dbms_random.VALUE * 100) bal FROM dual CONNECT BY ROWNUM <= 4

UNION

SELECT 002 card_code, ROWNUM q, trunc(dbms_random.VALUE * 100) bal FROM dual CONNECT BY ROWNUM <= 4;

SELECT * FROM t_change_lc;

SELECT a.card_code,

SUM(decode(a.q, 1, a.bal, 0)) q1,

SUM(decode(a.q, 2, a.bal, 0)) q2,

SUM(decode(a.q, 3, a.bal, 0)) q3,

SUM(decode(a.q, 4, a.bal, 0)) q4

FROM t_change_lc a

GROUP BY a.card_code

ORDER BY 1;

--行列转换 列转行

DROP TABLE t_change_cl;

CREATE TABLE t_change_cl AS

SELECT a.card_code,

SUM(decode(a.q, 1, a.bal, 0)) q1,

SUM(decode(a.q, 2, a.bal, 0)) q2,

SUM(decode(a.q, 3, a.bal, 0)) q3,

SUM(decode(a.q, 4, a.bal, 0)) q4

FROM t_change_lc a

GROUP BY a.card_code

ORDER BY 1;

SELECT * FROM t_change_cl;

SELECT t.card_code,

t.rn q,

decode(t.rn, 1, t.q1, 2, t.q2, 3, t.q3, 4, t.q4) bal

FROM (SELECT a.*, b.rn

FROM t_change_cl a,

(SELECT ROWNUM rn FROM dual CONNECT BY ROWNUM <= 4) b) t

ORDER BY 1, 2;

--行列转换 行转列 合并

DROP TABLE t_change_lc_comma;

CREATE TABLE t_change_lc_comma AS SELECT card_code,quarter_||q AS q FROM t_change_lc;

SELECT * FROM t_change_lc_comma;

SELECT t1.card_code, substr(MAX(sys_connect_by_path(t1.q, ;)), 2) q

FROM (SELECT a.card_code,

a.q,

row_number() over(PARTITION BY a.card_code ORDER BY a.q) rn

FROM t_change_lc_comma a) t1

START WITH t1.rn = 1

CONNECT BY t1.card_code = PRIOR t1.card_code

AND t1.rn - 1 = PRIOR t1.rn

GROUP BY t1.card_code;

--行列转换 列转行 分割

DROP TABLE t_change_cl_comma;

CREATE TABLE t_change_cl_comma AS

SELECT t1.card_code, substr(MAX(sys_connect_by_path(t1.q, ;)), 2) q

FROM (SELECT a.card_code,

a.q,

row_number() over(PARTITION BY a.card_code ORDER BY a.q) rn

FROM t_change_lc_comma a) t1

START WITH t1.rn = 1

CONNECT BY t1.card_code = PRIOR t1.card_code

AND t1.rn - 1 = PRIOR t1.rn

GROUP BY t1.card_code;

SELECT * FROM t_change_cl_comma;

SELECT t.card_code,

substr(t.q,

instr(; || t.q, ;, 1, rn),

instr(t.q || ;, ;, 1, rn) - instr(; || t.q, ;, 1, rn)) q

FROM (SELECT a.card_code, a.q, b.rn

FROM t_change_cl_comma a,

(SELECT ROWNUM rn FROM dual CONNECT BY ROWNUM <= 100) b

WHERE instr(; || a.q, ;, 1, rn) > 0) t

ORDER BY 1, 2;

-- 实现一条记录根据条件多表插入

DROP TABLE t_ia_src;

CREATE TABLE t_ia_src AS SELECT a||ROWNUM c1, b||ROWNUM c2 FROM dual CONNECT BY ROWNUM<=5;

DROP TABLE t_ia_dest_1;

CREATE TABLE t_ia_dest_1(flag VARCHAR2(10) , c VARCHAR2(10));

DROP TABLE t_ia_dest_2;

CREATE TABLE t_ia_dest_2(flag VARCHAR2(10) , c VARCHAR2(10));

DROP TABLE t_ia_dest_3;

CREATE TABLE t_ia_dest_3(flag VARCHAR2(10) , c VARCHAR2(10));

SELECT * FROM t_ia_src;

SELECT * FROM t_ia_dest_1;

SELECT * FROM t_ia_dest_2;

SELECT * FROM t_ia_dest_3;

INSERT ALL

WHEN (c1 IN (a1,a3)) THEN

INTO t_ia_dest_1(f