我有一个简单的问题——我有两个表（表a和表b），每个表都有工作人员的记录。一名工作人员可能反映在两个表中。我试图组合一个case语句，返回表a中某个雇员的第一个匹配项，然后退出case语句（即，不要试图在表b中找到同一个雇员）。现在，我的当前代码返回表a和表b中该雇员的匹配项。我怎样才能阻止这一切？
rfbsl7qr1#
这样怎么样：
with AllStaff as( select 1 as Level, StaffId, Name from TableA union all select 2 as Level, StaffId, Name from TableB),DistinctStaff as( select distinct StaffId from AllStaff)select s.StaffId, sRec.*from DistinctStaff as s outer apply (select top(1) * from AllStaff as a where a.StaffId = s.StaffId order by a.Level) as sRecgroup by s.StaffId
with AllStaff as
(
select 1 as Level, StaffId, Name
from TableA
union all
select 2 as Level, StaffId, Name
from TableB
),
DistinctStaff as
select distinct StaffId from AllStaff
)
select s.StaffId, sRec.*
from DistinctStaff as s
outer apply
(select top(1) * from AllStaff as a where a.StaffId = s.StaffId order by a.Level) as sRec
group by s.StaffId
1条答案
按热度按时间rfbsl7qr1#
这样怎么样：