ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 表的操作(merge,with)

表的操作(merge,with)

原创 Linux操作系统 作者:ljs4053 时间:2011-04-02 09:22:52 0 删除 编辑
1.虚表(with table_name as (statement) select :
  WITH t AS
(
  SELECT 1 C1,'a' C2, 'a' C3 FROM dual
  UNION ALL
  SELECT 10 C1,'x' C2, 'x' C3 FROM dual
  UNION ALL
  SELECT 2 C1,'y' C2, 'y+x' C3 FROM dual
  UNION ALL
  SELECT 3 C1,'c' C2, 'c+b+a' C3 FROM dual
  UNION ALL
   SELECT 1 C1,'z' C2, 'z+y+x' C3 FROM dual
  UNION ALL
   SELECT 2 C1,'b' C2, 'b+a' C3 FROM dual
),t1 as(
SELECT DISTINCT last_value(t.c1) over(PARTITION BY connect_by_root c3 ORDER BY LEVEL rows BETWEEN unbounded preceding AND unbounded following) AS c1_out,
                                t.c2,
                                t.c3
  FROM t
CONNECT BY nocycle instr(t.c3,
                                 PRIOR t.c3) > 0)
        SELECT *
          FROM t1
          ORDER BY t1.c1_out;
 
2.merge 融合语句(可以将通过条件将一表符合条件的数融入另一表,可避免重复)
 merge into table1
USING table2
on(table1.column1=table2.column1)
when matched then
  update set table1.column2=table2.column2
   when not matched THEN
 insert values(table2.column3....);
 
 COMMIT;
 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/22736841/viewspace-691558/,如需转载,请注明出处,否则将追究法律责任。

上一篇: exp/imp 测试
请登录后发表评论 登录
全部评论

注册时间:2010-02-22

  • 博文量
    14
  • 访问量
    10492