两张表的对比(同一关键字另外一个字段多个值对比

[sxbjxxr]

a表 b表
spdm ggdm spdm gg1dm
001 032 001 006
001 038 001 038
001 756 001 756
002 125 002 350
002 300 002 300


以a表为准，同一关键字spdm下，找出b表中gg1dm字段的值不在a表ggdm字段中的值

b表中 spdm 为001 gg1dm位006 的值找出来
b表中 spdm 为002 gg1dm位350 的值找出来

最终显示为b表
SPDM gg1dm
001 006
001 350

[二月十六]

--测试数据
if not object_id(N'Tempdb..#a') is null
	drop table #a
Go
Create table #a([spdm] nvarchar(23),[ggdm] nvarchar(23))
Insert #a
select N'001',N'032' union all
select N'001',N'038' union all
select N'001',N'756' union all
select N'002',N'125' union all
select N'002',N'300'
GO
if not object_id(N'Tempdb..#b') is null
	drop table #b
Go
Create table #b([spdm] nvarchar(23),[gg1dm] nvarchar(23))
Insert #b
select N'001',N'006' union all
select N'001',N'038' union all
select N'001',N'756' union all
select N'002',N'350' union all
select N'002',N'300'
Go
--测试数据结束
SELECT  *
FROM    #b
WHERE   NOT EXISTS ( SELECT *
                     FROM   #a
                     WHERE  #a.spdm = #b.spdm
                            AND #a.ggdm = #b.gg1dm )

[顺势而为1]

if not object_id('tempdb..#tmp_A') is null 
      drop table #tmp_A
go

create table #tmp_A(
        spdm varchar(10),
		ggdm varchar(10))
go

insert into #tmp_A 
Select '001','032' union all
Select '001','038' union all
Select '001','756' union all
Select '002','125' union all
Select '002','300'

GO

if not object_id('tempdb..#tmp_B') is null 
      drop table #tmp_B

go
create table #tmp_B(
        spdm varchar(10),
		gg1dm varchar(10))
go

insert into #tmp_B
Select '001','006' union all
Select '001','038' union all
Select '001','756' union all
Select '002','350' union all
Select '002','300'

Select * 
FROM #tmp_B b
Where not exists (Select 1 From #tmp_A a Where a.spdm=b.spdm and a.ggdm=b.gg1dm)

spdm       gg1dm
---------- ----------
001        006
002        350