这样怎么样：

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