这样,要对表A和表B,它们的字段的结构都是一样,只是名称不同,
两个表要按相同的零件名,找出不相同的位号
表APartID Location
-------- ------------
HAMP-025-0001 C4
HAMP-025-0001 C5
HAMP-025-0001 C6
HAMP-026-0001 A3
HAMP-026-0002 A9表BPartID Location
-------- ------------
HAMP-025-0001 C4
HAMP-025-0001 C8
HAMP-025-0001 C6
HAMP-026-0001 A2
HAMP-026-0002 A9
如何查询出:PartID Location1 Location2
-------- ------------ ---------
HAMP-025-0001 C5 C8
HAMP-026-0001 A3 A2
.....
两个表要按相同的零件名,找出不相同的位号
表APartID Location
-------- ------------
HAMP-025-0001 C4
HAMP-025-0001 C5
HAMP-025-0001 C6
HAMP-026-0001 A3
HAMP-026-0002 A9表BPartID Location
-------- ------------
HAMP-025-0001 C4
HAMP-025-0001 C8
HAMP-025-0001 C6
HAMP-026-0001 A2
HAMP-026-0002 A9
如何查询出:PartID Location1 Location2
-------- ------------ ---------
HAMP-025-0001 C5 C8
HAMP-026-0001 A3 A2
.....
select a.PartID,a.Location,b.Location
from [表A] a
inner join [表B] b
on a.PartID=b.PartID
where a.Location<>b.Location
;with ach as
(
select * from a
union all
select * from b
)select *
from ach t
where not exists (select 1 from ach partid=t.partid and location=t.location group by partid,location having count(1)>1)--这是两表不同的,然后去行转列!
union
select * from B except select * from A
UNION ALL 显示全部包括重复的
1.查询出partid的不重复清单
2.update得到表1中有,而不存在表2中的Location
3.update得到表2中有,而不存在表1中的Location