Oracle SQL精妙SQL語句講解

本篇文章由 泉州SEO www.234yp.com 整理髮布，mssql www.234yp.com/Article/168186.html 謝謝合作！
mssql

　　--行列轉換 行轉列
　　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;

　　-- 模擬批量提交http://blog.knowsky.com/

　　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;
　　END LOOP;
　　COMMIT;
　　END;

　　-- 真正的批量提交
　　DECLARE
　　CURSOR cur IS
　　SELECT * FROM user_objects;
　　TYPE rec IS TABLE OF user_objects%ROWTYPE;
　　recs rec;
　　BEGIN
　　OPEN cur;
　　WHILE (TRUE) LOOP
　　FETCH cur BULK COLLECT
　　INTO recs LIMIT 100;
　　-- forall 實現批量

　　FORALL i IN 1 .. recs.COUNT
　　INSERT INTO t_loop VALUES recs (i);
　　COMMIT;
　　EXIT WHEN cur%NOTFOUND;
　　END LOOP;
　　CLOSE cur;
　　END;

　　-- 悲觀鎖定/樂觀鎖定
　　DROP TABLE t_lock PURGE;
　　CREATE TABLE t_lock AS SELECT 1 ID FROM dual;

　　SELECT * FROM t_lock;

　　-- 常見的實現邏輯，隱含bug
　　DECLARE
　　v_cnt NUMBER;
　　BEGIN
　　-- 這裡有併發性的bug
　　SELECT MAX(ID) INTO v_cnt FROM t_lock;

　　-- here for other operation
　　v_cnt := v_cnt + 1;
　　INSERT INTO t_lock (ID) VALUES (v_cnt);
　　COMMIT;
　　END;

　　-- 高併發環境下，安全的實現邏輯
　　DECLARE
　　v_cnt NUMBER;
　　BEGIN
　　-- 對指定的行取得lock
　　SELECT ID INTO v_cnt FROM t_lock WHERE ID=1 FOR UPDATE;
　　-- 在有lock的情況下繼續下面的操作
　　SELECT MAX(ID) INTO v_cnt FROM t_lock;

　　-- here for other operation
　　v_cnt := v_cnt + 1;
　　INSERT INTO t_lock (ID) VALUES (v_cnt);
　　COMMIT; --提交併且釋放lock
　　END;

　　-- 硬解析/軟解析
　　DROP TABLE t_hard PURGE;
　　CREATE TABLE t_hard (ID INT);

　　SELECT * FROM t_hard;

　　DECLARE
　　sql_1 VARCHAR2(200);
　　BEGIN
　　-- hard parse
　　-- java中的同等語句是 Statement.execute()
　　FOR i IN 1 .. 1000 LOOP
　　sql_1 := 'insert into t_hard(id) values(' || i || ')';
　　EXECUTE IMMEDIATE sql_1;
　　END LOOP;
　　COMMIT;

　　-- soft parse
　　--java中的同等語句是 PreparedStatement.execute()
　　sql_1 := 'insert into t_hard(id) values(:id)';
　　FOR i IN 1 .. 1000 LOOP
　　EXECUTE IMMEDIATE sql_1
　　USING i;
　　END LOOP;
　　COMMIT;
　　END;

　　 　　-- 正確的分頁演算法

　　SELECT *
　　FROM (SELECT a.*, ROWNUM rn
　　FROM (SELECT * FROM t_employees ORDER BY first_name) a
　　WHERE ROWNUM <= 500)
　　WHERE rn > 480 ;

　　-- 分頁演算法(why not this one)
　　SELECT a.*, ROWNUM rn
　　FROM (SELECT * FROM t_employees ORDER BY first_name) a
　　WHERE ROWNUM <= 500 AND ROWNUM > 480;

　　-- 分頁演算法(why not this one)

　　SELECT b.*
　　FROM (SELECT a.*, ROWNUM rn
　　FROM t_employees a
　　WHERE ROWNUM < = 500
　　ORDER BY first_name) b
　　WHERE b.rn > 480;

　　-- OLAP
　　-- 小計合計
　　SELECT CASE
　　WHEN a.deptno IS NULL THEN
　　'合計'
　　WHEN a.deptno IS NOT NULL AND a.empno IS NULL THEN
　　'小計'
　　ELSE
　　'' || a.deptno
　　END deptno,
　　a.empno,
　　a.ename,
　　SUM(a.sal) total_sal
　　FROM scott.emp a
　　GROUP BY GROUPING SETS((a.deptno),(a.deptno, a.empno, a.ename),());

　　-- 分組排序
　　SELECT a.deptno,
　　a.empno,
　　a.ename,
　　a.sal,
　　-- 可跳躍的rank
　　rank() over(PARTITION BY a.deptno ORDER BY a.sal DESC) r1,
　　-- 密集型rank
　　dense_rank() over(PARTITION BY a.deptno ORDER BY a.sal DESC) r2,
　　-- 不分組排序
　　rank() over(ORDER BY sal DESC) r3
　　FROM scott.emp a
　　ORDER BY a.deptno,a.sal DESC;

　　-- 當前行資料和前/後n行的資料比較
　　SELECT a.empno,
　　a.ename,
　　a.sal,
　　-- 上面一行
　　lag(a.sal) over(ORDER BY a.sal DESC) lag_1,
　　-- 下面三行
　　lead(a.sal, 3) over(ORDER BY a.sal DESC) lead_3
　　FROM scott.emp a
　　ORDER BY a.sal DESC;

關於更多mssql內容，可以收藏本網頁。
泉州夜網泉州新聞網是泉州新聞地區唯一的新聞入口網站