1-函数分组

隨機函數，分組排序，分組函數，多表插入或更新(merge)，批量綁定，通用函數1. rownum的區間查詢DROP TABLE t_a;CREATE TABLE t_a (card_code VARCHAR2(3), q NUMBER, bal NUMBER);INSERT INTO t_a 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_a;select * from t_a where rownum>=1;select * from t_a where rownum>=3;select * from t_a where rownum<=3;select * from t_a where rownum<3;select * from t_a where rownum>3;select * from t_a where rownum>1;select * from (select card_code,q,bal,row_number()over(order by bal) as num from t_a) where num>2 and num<6; SELECT ROWNUM r, t.* FROM t_a t ORDER BY t.bal DESC;為何行號是亂的？因先分配行號，再排序SELECT ROWNUM r, t.* FROM (SELECT t.* FROM t_a t ORDER BY t.bal DESC) t;應該先排序再分配行號2.行列轉換a.行轉列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_a aGROUP BY a.card_codeORDER BY1;b.列轉行DROP TABLE t_b;CREATE TABLE t_b 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_a a GROUP BY a.card_code ORDER BY 1; SELECT * FROM t_b;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_b a, (SELECT ROWNUM rn FROM dual CONNECT BY ROWNUM <= 4) b) tORDER BY1, 2;c.行轉列合併DROP TABLE t_c;CREATE TABLE t_c AS SELECT card_code, bal||q as q FROM t_a;SELECT * FROM t_c;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_c a) t1START WITH t1.rn = 1CONNECT BY t1.card_code = PRIOR t1.card_codeAND t1.rn - 1 = PRIOR t1.rnGROUP BY t1.card_code;d.列轉行分割DROP TABLE t_d;CREATE TABLE t_d 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_c 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 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_d a,(SELECT ROWNUM rn FROM dual CONNECT BY ROWNUM <= 100) bWHERE instr(';' || a.q, ';', 1, rn) > 0) tORDER BY1, 2;3.刪除重復記錄set timing on;Drop table test1;Create table test1 ( ID NUMBER,SEQ NUMBER);declarei number;j number;beginfor i in1..2loopfor j in1..10000loopinsert into test1values (j, j + 10);end loop;commit;end loop;end;/法一:使用臨時表進行刪除set timing on;create table test2 as select distinct*from test1;truncate table test1;insert into test1 select * from test1;rollback;法二: 使用自連接+rowid進行刪除select a.*, a.rowid, b.*, b.rowid from test1 a, test1 b where a.id = b.id and a.seq = b.seq and a.id =1; DELETE FROM test1 t1 WHERE t1.ROWID NOT IN (SELECT MAX(t2.rowid) FROM test1 t2 WHERE t1.id = t2.id ANDt1.seq = t2.seq);Rollback;DELETE FROM test1 WHERE ROWID NOT IN (SELECT MAX(ROWID) FROM test1 GROUP BY id, seq);Rollback;法三:用row_number():delete from test1where rowid not in (select t.rowidfrom (select d.id,d.seq,row_number() over(partition by id || seq order by id || seq) row_flagfrom test1d) twhere t.row_flag > 1);-- 某欄位重復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, FROM (SELECT a.code,,row_number() over(PARTITION BY a.code ORDER BY a.ROWID) rnFROM t_dup a) bWHERE b.rn > 1;delete t_dupwhere rowid in (SELECT b.rowidFROM (SELECT a.code,,row_number() over(PARTITION BY a.code ORDER BY a.ROWID) rnFROM t_dup a) bWHERE b.rn > 1);4.實現一條記錄根據條件多表插入DROP TABLE t_ia_src;CREATE TABLE t_ia_src AS SELECT'a'||ROWNUM c1, 'b'||ROWNUM c2FROM 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 (c1IN ('a1', 'a3')) THEN INTO t_ia_dest_1 (flag, c) VALUES (flag1, c2)WHEN (c1IN ('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'flag2FROM 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 = WHEN NOT MATCHED THENINSERT (code, NAME) VALUES (b.code, );5.通用函數select 1 from dual where null=null;select 1 from dual where null='';select 1 from dual where ''='';select 1 from dual where null is null;select 1 from dual where nvl(null,0)=nvl(null,0);select 1+null from dual;select 1-null from dual;select 1*null from dual;select 1/null from dual;conn scott/tiger@6088;select * from emp t;select * from emp t where comm like'%';select NVL(1000,12) FROM DUAL;select NVL(null,12) FROM DUAL;SELECT NVL2(12,1,-1) FROM dual;SELECT NVL2(null,1,-1) FROM dual;SELECT NULLIF(12,12) FROM dual;SELECT NULLIF(12,2) FROM dual;SELECT COALESCE(null,3,4,null) FROM dual;SELECT COALESCE(null,null,4,null) FROM dual;6. 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;-- 模擬批量提交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;-- 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;知識點1.學用dbms_random：取亂數的函數oracle中貌似沒有mssql中的rand()直接取亂數的函數。