Chinaunix首页 | 论坛 | 博客
  • 博客访问: 21452
  • 博文数量: 9
  • 博客积分: 1435
  • 博客等级: 上尉
  • 技术积分: 110
  • 用 户 组: 普通用户
  • 注册时间: 2009-01-09 15:25
文章分类
文章存档

2012年(1)

2011年(1)

2009年(7)

我的朋友
最近访客

分类: Oracle

2012-11-13 21:18:45

--行列转换 行转列
  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
   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;
阅读(272) | 评论(0) | 转发(0) |
0

上一篇:博客已升级,请注意变更地址

下一篇:没有了

给主人留下些什么吧!~~