1. A,B,C,D四张表的结构相同,都包含四个字段:id, name, address, age;
Q1:存在于A表中,不存在于B表和D表中的数据
Q2:将不属于表D的但是在表A中的数据插入到表B中去
A1: 查询在一张表不在另一张表中的记录:
首先将表B和D组合(UNION),然后从A表中去掉组合表中的记录;
首先想到的是使用not in的方法,e.g. select * from table where column not in (another table set);当这样做相当于表a中字段都要遍历一次b表,效率低下。
方法1:使用外连接
select A.* from A left join (select * from B union select * from D) as TEMP on A.id = TEMP.id and A.name = TEMP.name and A.age = TEMP.age and A.addresss = TEMP.addresss where TEMP.id IS NULL
方法2:sql server中,使用关键字 EXCEPT
select A.* from A except (select * from B union select * from D)
表A:
id name addresss age
1 a adda 10
2 b addb 11
3 c addc 12
表B:
id name addresss age
3 TEST addc 12
表D:
id name addresss age
2 b addb 11
结果:
id name addresss age
1 a adda 10
3 c addc 12
A2:
insert into B select A.* from A left join D on A.id = D.id and A.name = D.name and A.age = D.age and A.addresss = D.addresss where D.id IS NULL and A.id not in (select id from B)
2. 表A与表B结构一致,现找出表B中,表B与表A的三个对应字段中有两个对应字段相等的记录:
如:
表A
col1 col2 col3
1 10 100
2 20 200
3 30 300
4 40 400
表B
col1 col2 col3
1 20 1
2 20 222
1 102 111
3 30 300
9 40 400
输出:
col1 col2 col3
2 20 222
3 30 300
9 40 400
select B.* from B left join A on (
(B.col1 = A.col1 and B.col2 = A.col2) or
(B.col2 = A.col2 and B.col3 = A.col3) or
(B.col1 = A.col1 and B.col3 = A.col3) )
where A.col1 is not null