表1
公司 箱号
A A1
A A2
A A3
B B1
B B2
C C1 表2
公司 箱号
A A1
D D1 E E1 结果表数据是
箱号 操作 (1为添加,0为减少)
A2 1
A3 1
B1 1
B2 1
C1 1
D1 0
E 0 求SQL
公司 箱号
A A1
A A2
A A3
B B1
B B2
C C1 表2
公司 箱号
A A1
D D1 E E1 结果表数据是
箱号 操作 (1为添加,0为减少)
A2 1
A3 1
B1 1
B2 1
C1 1
D1 0
E 0 求SQL
(
Company VARCHAR2(20),
ContainerNo VARCHAR2(20)
);
INSERT INTO T176 VALUES('A', 'A1');
INSERT INTO T176 VALUES('A', 'A2');
INSERT INTO T176 VALUES('A', 'A3');
INSERT INTO T176 VALUES('B', 'B1');
INSERT INTO T176 VALUES('B', 'B2');
INSERT INTO T176 VALUES('C', 'C1');CREATE TABLE T177
(
Company VARCHAR2(20),
ContainerNo VARCHAR2(20)
);
INSERT INTO T177 VALUES('A', 'A1');
INSERT INTO T177 VALUES('D', 'D1');
INSERT INTO T177 VALUES('E', 'E1');
结果:
select 'A' company,'A1' boxnum from dual union all
select 'A' ,'A2' from dual union all
select 'A' ,'A3' from dual union all
select 'B' ,'B1' from dual union all
select 'B' ,'B2' from dual union all
select 'C' ,'C1' from dual),
t2 as(
select 'A' company,'A1' boxnum from dual union all
select 'D' ,'D1' from dual union all
select 'E' ,'E1' from dual)select company,boxnum,1 action from(
select company,boxnum from t1
minus
select company,boxnum from t2)--表1有 表2没的,即添加
union all
select company,boxnum,0 action from(
select company,boxnum from t2
minus
select company,boxnum from t1)--表2有 表1没的,即减少
select * from
(select distinct tb1.containerno,1 cz from tb1
union all
select distinct tb2.containerno,0 cz from tb2)
where containerno not in
(select tb1.containerno from tb1,tb2 where tb1.containerno=tb2.containerno) containerno cz
----------------------------------------------
1 C1 1
2 A2 1
3 A3 1
4 B1 1
5 B2 1
6 D1 0
7 E1 0