有三张表,第一张的Manager,Boss,要从第二张和第三张表得出,如下图,问题是第二,三两张表提供的Manager,Boss有重复的要去掉,有不同的要再生一行保留
Table1:
----------------------------------------------------
Mtag Puid location Subloc Manager Boss
asd 123 xxxsd xdfx
we 345 xsdfxx xgfnhx
asg 456 xxxcx xgfhx
ws 567 xxsdfsx xxfgh
dr 789 sdxx xfghxxx
----------------------------
---------------
table2:
---
Manager Boss Mtag
rain asd
rock yoyo we
jeffy asg
fukon ws
marana jack dr
-------------------------
--------------
table3
Manager Boss Puid
Rain kanong 123
wking 345
jasen jeffy 456
lindlo 567
marana jack 789 结果是:
Mtag Puid location Subloc Manager Boss
-------------------------------------------------
asd 123 xxxsd xdfx rain kanong
we 345 xsdfxx xgfnhx rock yoyo
we 345 xsdfxx xgfnhx wking yoyo
asg 456 xxxcx xgfhx jasen jeffy
ws 567 xxsdfsx xxfgh fukon lindlo
dr 789 sdxx xfghxxx marana jack
可用下面CODE来建立测试环境
-----------
declare @table1 table(Mtag varchar(20),Puid int,location varchar(20),Subloc varchar(20))
insert into @table1
select 'asd',123,'xxxsd','xdfx' union all
select 'we',345,'xsdfxx','xgfnhx' union all
select 'asg',456,'xxxcx','xgfhx' union all
select 'ws',567,'xxsdfsx','xxfgh' union all
select 'dr',789,'sdxx','xfghxxx'
declare @table2 table(Manager varchar(20),Boss varchar(20),Mtag varchar(20))
insert into @table2
select 'rain','','asd' union all
select 'rock','yoyo','we' union all
select '','jeffy','asg' union all
select 'fukon','','ws' union all
select 'marana','jack','dr'
declare @table3 table(Manager varchar(20),Boss varchar(20),Puid int)
insert into @table3
select 'rain','kanong',123 union all
select 'wking','',345 union all
select 'jasen','jeffy',456 union all
select '','lindlo',567 union all
select 'marana','jack',789
Table1:
----------------------------------------------------
Mtag Puid location Subloc Manager Boss
asd 123 xxxsd xdfx
we 345 xsdfxx xgfnhx
asg 456 xxxcx xgfhx
ws 567 xxsdfsx xxfgh
dr 789 sdxx xfghxxx
----------------------------
---------------
table2:
---
Manager Boss Mtag
rain asd
rock yoyo we
jeffy asg
fukon ws
marana jack dr
-------------------------
--------------
table3
Manager Boss Puid
Rain kanong 123
wking 345
jasen jeffy 456
lindlo 567
marana jack 789 结果是:
Mtag Puid location Subloc Manager Boss
-------------------------------------------------
asd 123 xxxsd xdfx rain kanong
we 345 xsdfxx xgfnhx rock yoyo
we 345 xsdfxx xgfnhx wking yoyo
asg 456 xxxcx xgfhx jasen jeffy
ws 567 xxsdfsx xxfgh fukon lindlo
dr 789 sdxx xfghxxx marana jack
可用下面CODE来建立测试环境
-----------
declare @table1 table(Mtag varchar(20),Puid int,location varchar(20),Subloc varchar(20))
insert into @table1
select 'asd',123,'xxxsd','xdfx' union all
select 'we',345,'xsdfxx','xgfnhx' union all
select 'asg',456,'xxxcx','xgfhx' union all
select 'ws',567,'xxsdfsx','xxfgh' union all
select 'dr',789,'sdxx','xfghxxx'
declare @table2 table(Manager varchar(20),Boss varchar(20),Mtag varchar(20))
insert into @table2
select 'rain','','asd' union all
select 'rock','yoyo','we' union all
select '','jeffy','asg' union all
select 'fukon','','ws' union all
select 'marana','jack','dr'
declare @table3 table(Manager varchar(20),Boss varchar(20),Puid int)
insert into @table3
select 'rain','kanong',123 union all
select 'wking','',345 union all
select 'jasen','jeffy',456 union all
select '','lindlo',567 union all
select 'marana','jack',789
a.Mtag
,a.Puid
,location
,Subloc
,Manager
,Boss
from @table1 a
join @table2 b on b.Mtag=a.Mtag
union
select
a.Mtag
,a.Puid
,location
,Subloc
,Manager
,Boss
from @table1 a
join @table3 c on c.Puid=a.Puid
where not exists (select 1
from @table1 a0
join @table2 b0 on b0.Mtag=a0.Mtag
where a0.Mtag=a.Mtag and a0.Puid=a.Puid
and c.Manager=b0.Manager
and c.Boss=b0.Boss)
-- Mtag Puid location Subloc Manager Boss
-- asd 123 xxxsd xdfx rain
-- asd 123 xxxsd xdfx rain kanong
-- asg 456 xxxcx xgfhx jeffy
-- asg 456 xxxcx xgfhx jasen jeffy
-- dr 789 sdxx xfghxxx marana jack
-- we 345 xsdfxx xgfnhx rock yoyo
-- we 345 xsdfxx xgfnhx wking
-- ws 567 xxsdfsx xxfgh lindlo
-- ws 567 xxsdfsx xxfgh fukon
--
select a.Mtag1,a.Puid1 ,a.location,a.Subloc,
Manager = (case when c.Manager='' then b.Manager else c.Manager end),
boss = (case when c.Boss='' then b.Boss else c.Boss end)
from @table1 a left join @table2 b on a.Mtag1 = b.Mtag left join @table3 c on a.Puid1 = c.Puid
/*
Mtag1 Puid1 location Subloc Manager boss
-------------------- ----------- -------------------- -------------------- -------------------- --------------------
asd 123 xxxsd xdfx rain kanong
we 345 xsdfxx xgfnhx wking yoyo
asg 456 xxxcx xgfhx jasen jeffy
ws 567 xxsdfsx xxfgh fukon lindlo
dr 789 sdxx xfghxxx marana jack(5 row(s) affected)*/
CASE WHEN isnumeric(aaa.mtag)=1 THEN puid ELSE aaa.mtag END mtag,
CASE WHEN isnumeric(aaa.puid)=1 THEN aaa.puid ELSE aaa.mtag END puid,
location,subloc,manager,boss
FROM
(
SELECT mtag,cast(puid AS varchar) puid,location,subloc FROM @table1
UNION ALL
SELECT cast(puid AS varchar),mtag,location,subloc FROM @table1
)aaa
INNER JOIN
(
SELECT
CASE WHEN Manager='' THEN (SELECT manager FROM @table3 c WHERE EXISTS(SELECT 1 FROM @table1 WHERE mtag=aa.mtag and puid=c.puid)) ELSE manager END manager,
CASE WHEN boss='' THEN (SELECT boss FROM @table3 c WHERE EXISTS(SELECT 1 FROM @table1 WHERE mtag=aa.mtag and puid=c.puid)) ELSE boss END boss,
Mtag FROM @table2 aa
UNION ALL
SELECT CASE WHEN Manager='' THEN (SELECT manager FROM @table2 c WHERE EXISTS(SELECT 1 FROM @table1 WHERE puid=bb.puid and mtag=c.mtag)) ELSE manager END,
CASE WHEN boss='' THEN (SELECT boss FROM @table2 c WHERE EXISTS(SELECT 1 FROM @table1 WHERE puid=bb.puid and mtag=c.mtag)) ELSE boss end,
cast(puid AS varchar) FROM @table3 bb
)bbb
ON aaa.mtag=bbb.mtag
ORDER BY aaa.puid
--result
/*mtag puid location subloc manager boss
------------------------------ ------------------------------ -------------------- -------------------- -------------------- --------------------
asd 123 xxxsd xdfx rain kanong
we 345 xsdfxx xgfnhx rock yoyo
we 345 xsdfxx xgfnhx wking yoyo
asg 456 xxxcx xgfhx jasen jeffy
ws 567 xxsdfsx xxfgh fukon lindlo
dr 789 sdxx xfghxxx marana jack(所影响的行数为 6 行)*/