34,576
社区成员
发帖
与我相关
我的任务
分享
--测试数据
if not object_id(N'Tempdb..#a') is null
drop table #a
Go
Create table #a([danhao] nvarchar(23),[jine] nvarchar(23))
Insert #a
select N'单号1',N'金额1' union all
select N'单号2',N'金额2'
GO
if not object_id(N'Tempdb..#b') is null
drop table #b
Go
Create table #b([danhao] nvarchar(23),[renyuan] nvarchar(25))
Insert #b
select N'单号1',N'人员id1'
GO
if not object_id(N'Tempdb..#c') is null
drop table #c
Go
Create table #c([danhao] nvarchar(23),[renyuan] nvarchar(25))
Insert #c
select N'单号2',N'人员id2'
GO
if not object_id(N'Tempdb..#人员表') is null
drop table #人员表
Go
Create table #人员表([id] nvarchar(25),[renyuan] nvarchar(23))
Insert #人员表
select N'人员id1',N'人员a' union all
select N'人员id2',N'人员b'
Go
--测试数据结束
SELECT #a.*,#人员表.renyuan
FROM #a
LEFT JOIN #b ON #b.danhao = #a.danhao
LEFT JOIN #c ON #c.danhao = #a.danhao
LEFT JOIN #人员表 ON ( CASE WHEN #b.renyuan IS NOT NULL THEN #b.renyuan
ELSE #c.renyuan
END ) = #人员表.id
with 表a(code,price)
as(
select '单号1','金额1' union all
select '单号2','金额2'),
表b(code,personId)
as(
select '单号1','人员id1'
),表c(code,personId)
as(
select '单号2','人员id2'
),
人员表(id,name)
as(
select '人员id1','人员a' union all
select '人员id2','人员b'
)
select a.code,a.price,c.name from 表a a join (select * from 表b union select * from 表c)b on a.code=b.code join 人员表 c on b.personId=c.id