问按顺序从多个表查询数据

EN

; with item as
(
    select item from T1
    union
    select item from T2
    union
    select item from T3
)
select    item  = i.item,
          value = coalesce(v1.value, v2.value, v3.value)
from      item i
left join T1 v1  on i.item  = v1.item
left join T2 v2  on i.item  = v2.item
left join T3 v3  on i.item  = v3.item

create table #item1(
    item int,
    value int
)

insert into #item1 values
(1,1),
(2,2)

create table #item2(
    item int,
    value int
)

insert into #item2 values
(1,10),
(2,20),
(3,30)

create table #item3(
    item int,
    value int
)

insert into #item3 values
(1,100),
(2,200),
(3,300),
(4,400)

;with cte as (
    select *, t=1 from #item1
    union all
    select *, t=2 from #item2
    union all
    select *, t=3 from #item3
),
cte1 as (
    select
        *,
        rn = row_number() over ( partition by item order by t )
    from
        cte
)
select
    item, value
from
    cte1
where
    rn = 1


drop table #item1
drop table #item2
drop table #item3

select
 M.item  --Select unique item from Master list
 ,coalesce(
  (select price from T1 where T1.item = M.item),
  (select price from T2 where T2.item = M.item),
  (select price from T3 where T3.item = M.item)
 )  --Cascade of prices per table
from 
  (select * from T1
  union
  select * from T2
  union 
  select * from T3) M  --Creating Master list of items and prices
group by item  --ensuring that we only pull unique items