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 <= 4UNION 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)) q4FROM t_change_lc aGROUP BY a.card_codeORDER 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)) q4FROM t_change_lc aGROUP BY a.card_codeORDER 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) balFROM (SELECT a.*, b.rnFROM t_change_cl a,(SELECT ROWNUM rn FROM dual CONNECT BY ROWNUM <= 4) b) tORDER 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) qFROM (SELECT a.card_code,a.q,row_number() over(PARTITION BY a.card_code ORDER BY a.q) rnFROM t_change_lc_comma a) t1START WITH t1.rn = 1CONNECT BY t1.card_code = PRIOR t1.card_codeAND t1.rn - 1 = PRIOR t1.rnGROUP BY t1.card_code;--行列转换 列转行 分割DROP TABLE t_change_cl_comma;CREATE TABLE t_change_cl_comma ASSELECT t1.card_code, substr(MAX(sys_connect_by_path(t1.q, ;)), 2) qFROM (SELECT a.card_code,a.q,row_number() over(PARTITION BY a.card_code ORDER BY a.q) rnFROM t_change_lc_comma a) t1START WITH t1.rn = 1CONNECT BY t1.card_code = PRIOR t1.card_codeAND t1.rn - 1 = PRIOR t1.rnGROUP 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)) qFROM (SELECT a.card_code, a.q, b.rnFROM t_change_cl_comma a,(SELECT ROWNUM rn FROM dual CONNECT BY ROWNUM <= 100) bWHERE instr(; || a.q, ;, 1, rn) > 0) tORDER 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 ALLWHEN (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)ELSEINTO 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 aUSING (SELECT the code code, the name NAME FROM dual) bON (a.code = b.code)WHEN MATCHED THENUPDATE SET a.NAME = b.NAMEWHEN NOT MATCHED THENINSERT (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.NAMEFROM (SELECT a.code,a.NAME,row_number() over(PARTITION BY a.code ORDER BY a.ROWID) rnFROM t_dup a) bWHERE b.rn > 1;-- IN/EXISTS的不同适用环境-- t_orders.customer_id有索引SELECT a.*FROM t_employees aWHERE a.employee_id IN(SELECT b.sales_rep_id FROM t_orders b WHERE b.customer_id = 12);SELECT a.*FROM t_employees aWHERE EXISTS (SELECT 1FROM t_orders bWHERE b.customer_id = 12AND a.employee_id = b.sales_rep_id);-- t_employees.department_id有索引SELECT a.*FROM t_employees aWHERE a.department_id = 10AND EXISTS(SELECT 1 FROM t_orders b WHERE a.employee_id = b.sales_rep_id);SELECT a.*FROM t_employees aWHERE a.department_id = 10AND a.employee_id IN (SELECT b.sales_rep_id FROM t_orders b);-- FBIDROP TABLE t_fbi;CREATE TABLE t_fbi ASSELECT 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/ROLLBACKDROP TABLE t_loop PURGE;create TABLE t_loop AS SELECT * FROM user_objects WHERE 1=2;SELECT * FROM t_loop;-- 逐行提交DECLAREBEGINFOR cur IN (SELECT * FROM user_objects) LOOPINSERT INTO t_loop VALUES cur;COMMIT;END LOOP;END;-- 模拟批量提交http://blog.knowsky.com/DECLAREv_count NUMBER;BEGINFOR cur IN (SELECT * FROM user_objects) LOOPINSERT INTO t_loop VALUES cur;v_count := v_count + 1;IF v_count >= 100 THENCOMMIT;END IF;END LOOP;COMMIT;END;-- 真正的批量提交DECLARECURSOR cur ISSELECT * FROM user_objects;TYPE rec IS TABLE OF user_objects%ROWTYPE;recs rec;BEGINOPEN cur;WHILE (TRUE) LOOPFETCH cur BULK COLLECTINTO recs LIMIT 100;-- forall 实现批量FORALL i IN 1 .. recs.COUNTINSERT 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;-- 常见的实现逻辑，隐含bugDECLAREv_cnt NUMBER;BEGIN-- 这里有并发性的bugSELECT MAX(ID) INTO v_cnt FROM t_lock;-- here for other operationv_cnt := v_cnt + 1;INSERT INTO t_lock (ID) VALUES (v_cnt);COMMIT;END;-- 高并发环境下，安全的实现逻辑DECLAREv_cnt NUMBER;BEGIN-- 对指定的行取得lockSELECT 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 operationv_cnt := v_cnt + 1;INSERT INTO t_lock (ID) VALUES (v_cnt);COMMIT; --提交并且释放lockEND;-- 硬解析/软解析DROP TABLE t_hard PURGE;CREATE TABLE t_hard (ID INT);SELECT * FROM t_hard;DECLAREsql_1 VARCHAR2(200);BEGIN-- hard parse-- java中的同等语句是 Statement.execute()FOR i IN 1 .. 1000 LOOPsql_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 LOOPEXECUTE IMMEDIATE sql_1USING i;END LOOP;COMMIT;END; -- 正确的分页算法 SELECT *FROM (SELECT a.*, ROWNUM rnFROM (SELECT * FROM t_employees ORDER BY first_name) aWHERE ROWNUM <= 500)WHERE rn > 480 ;-- 分页算法(why not this one)SELECT a.*, ROWNUM rnFROM (SELECT * FROM t_employees ORDER BY first_name) aWHERE ROWNUM <= 500 AND ROWNUM > 480;-- 分页算法(why not this one)SELECT b.*FROM (SELECT a.*, ROWNUM rnFROM t_employees aWHERE ROWNUM < = 500ORDER BY first_name) bWHERE b.rn > 480;-- OLAP-- 小计合计SELECT CASEWHEN a.deptno IS NULL THEN合计WHEN a.deptno IS NOT NULL AND a.empno IS NULL THEN小计ELSE|| a.deptnoEND deptno,a.empno,a.ename,SUM(a.sal) total_salFROM scott.emp aGROUP BY GROUPING SETS((a.deptno),(a.deptno, a.empno, a.ename),());-- 分组排序SELECT a.deptno,a.empno,a.ename,a.sal,-- 可跳跃的rankrank() over(PARTITION BY a.deptno ORDER BY a.sal DESC) r1,-- 密集型rankdense_rank() over(PARTITION BY a.deptno ORDER BY a.sal DESC) r2,-- 不分组排序rank() over(ORDER BY sal DESC) r3FROM scott.emp aORDER 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_3FROM scott.emp aORDER BY a.sal DESC; 

转载：应原有者的要求没有去掉他的广告，抱歉！

文章整理：西部数码--专业提供域名注册、虚拟主机服务
http://www.west263.com以上信息与文章正文是不可分割的一部分,如果您要转载本文章,请保留以上信息，谢谢!