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;
(
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
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;
insert values(table2.column3....);
COMMIT;
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/22736841/viewspace-691558/ ，如需转载，请注明出处，否则将追究法律责任。
上一篇：
exp/imp 测试
下一篇：
高级分组（rollup,cube操作)
请登录后发表评论
登录
全部评论
