Oracle SQL精妙SQL语句讲解 2008-11-19 15:57 --行列转换 行转列
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(flag,c) VALUES(flag1,c2) WHEN (c1 IN ('a2','a4')) THEN
INTO t_ia_dest_2(flag,c) VALUES(flag2,c2) ELSE
INTO t_ia_dest_3(flag,c) VALUES(flag1||flag2,c1||c2) SELECT c1,c2, 'f1' flag1, 'f2' flag2 FROM t_ia_src;
-- 如果存在就更新，不存在就插入用一个语句实现 DROP TABLE t_mg;
CREATE TABLE t_mg(code VARCHAR2(10), NAME VARCHAR2(10));
SELECT * FROM t_mg;
MERGE INTO t_mg a
USING (SELECT 'the code' code, 'the name' NAME FROM dual) b ON (a.code = b.code) WHEN MATCHED THEN
UPDATE SET a.NAME = b.NAME WHEN NOT MATCHED THEN
INSERT (code, NAME) VALUES (b.code, b.NAME);
-- 抽取/删除重复记录
DROP TABLE t_dup;
CREATE TABLE t_dup AS SELECT 'code_'||ROWNUM code,
dbms_random.string('z',5) NAME FROM dual CONNECT BY ROWNUM<=10; INSERT INTO t_dup SELECT 'code_'||ROWNUM code, dbms_random.string('z',5) NAME FROM dual CONNECT BY ROWNUM<=2;
SELECT * FROM t_dup;
SELECT * FROM t_dup a WHERE a.ROWID <> (SELECT MIN(b.ROWID) FROM t_dup b WHERE a.code=b.code);
SELECT b.code, b.NAME FROM (SELECT a.code, a.NAME,
row_number() over(PARTITION BY a.code ORDER BY a.ROWID) rn
FROM t_dup a) b WHERE b.rn > 1;
-- IN/EXISTS的不同适用环境 -- t_orders.customer_id有索引 SELECT a.*
FROM t_employees a
WHERE a.employee_id IN
(SELECT b.sales_rep_id FROM t_orders b WHERE b.customer_id = 12);
SELECT a.*
FROM t_employees a
WHERE EXISTS (SELECT 1 FROM t_orders b
WHERE b.customer_id = 12
AND a.employee_id = b.sales_rep_id);
-- t_employees.department_id有索引 SELECT a.*
FROM t_employees a
WHERE a.department_id = 10 AND EXISTS
(SELECT 1 FROM t_orders b WHERE a.employee_id = b.sales_rep_id);
SELECT a.*
FROM t_employees a
WHERE a.department_id = 10
AND a.employee_id IN (SELECT b.sales_rep_id FROM t_orders b);
-- FBI
DROP TABLE t_fbi;
CREATE TABLE t_fbi AS
SELECT ROWNUM rn, dbms_random.STRING('z',10) NAME , SYSDATE + dbms_random.VALUE * 10 dt FROM dual CONNECT BY ROWNUM <=10;
CREATE INDEX idx_nonfbi ON t_fbi(dt);
DROP INDEX idx_fbi_1;
CREATE INDEX idx_fbi_1 ON t_fbi(trunc(dt));
SELECT * FROM t_fbi WHERE trunc(dt) = to_date('2006-09-21','yyyy-mm-dd') ;
-- 不建议使用
SELECT * FROM t_fbi WHERE to_char(dt, 'yyyy-mm-dd') = '2006-09-21';
-- LOOP中的COMMIT/ROLLBACK DROP TABLE t_loop PURGE;
create TABLE t_loop AS SELECT * FROM user_objects WHERE 1=2;
SELECT * FROM t_loop;
-- 逐行提交 DECLARE BEGIN
FOR cur IN (SELECT * FROM user_objects) LOOP INSERT INTO t_loop VALUES cur; COMMIT; END LOOP; END;
-- 模拟批量提交 DECLARE
v_count NUMBER; BEGIN
FOR cur IN (SELECT * FROM user_objects) LOOP INSERT INTO t_loop VALUES cur; v_count := v_count + 1; IF v_count >= 100 THEN COMMIT; END IF;