有两张表,表A和表B,
表A:id,code,name
表B: id,code,name创建了一张视图,V_A,
select * from A
union all
select * from B为了控制ID,没有重复的,我需要对表A和表B做如何控制? 请高手讲解详细点,谢谢!
表A:id,code,name
表B: id,code,name创建了一张视图,V_A,
select * from A
union all
select * from B为了控制ID,没有重复的,我需要对表A和表B做如何控制? 请高手讲解详细点,谢谢!
union all
select * from B where not exisits
(select 1 from a where b.id= a.id)
(select 1 from b where a.id= b.id)union all
select * from B
这样无论A表还是B表中都不会有重复的记录。然后创建视图的时候,使用UNION,不带ALL,A和B中重复的记录,只取一条到视图中。
(
ID NUMBER(4) PRIMARY KEY,
NAME VARCHAR2(20)
);
INSERT INTO T72 VALUES(1, 'A');
INSERT INTO T72 VALUES(2, 'B');
INSERT INTO T72 VALUES(3, 'C');
CREATE TABLE T73
(
ID NUMBER(4) PRIMARY KEY,
NAME VARCHAR2(20)
);
INSERT INTO T73 VALUES(1, 'A');
INSERT INTO T73 VALUES(2, 'B');
INSERT INTO T73 VALUES(4, 'D');
INSERT INTO T73 VALUES(5, 'E');
实测结果:
select id,code,name,row_number() over(partition by id order by id) rn from V_A ) where rn=1
select * from A
union all
select * from B where not exisits
(select 1 from a where b.id= a.id)