这个标题不知道能不能让大家理解,需求描述比较绕,举个例子,一个用户表,一个联系人表,一个产品表,三个表想要关联,联系人和产品中有用户主键Id,但是直接join三个表,会出现多个重复的问题,而且有的用户产品多,有的用户联系人多,想要一个表看清楚,直接上代码例子:
--测试数据
if not object_id(N'Tempdb..#展商') is null
drop table #展商
Go
Create table #展商([Id] int,[Company] nvarchar(23))
Insert #展商
select 1,N'企业A' union all
select 2,N'企业B'
GO
if not object_id(N'Tempdb..#联系人') is null
drop table #联系人
Go
Create table #联系人([Id] int,[UserId] int,[Name] nvarchar(22))
Insert #联系人
select 1,1,N'张三' union all
select 2,1,N'李四' union all
select 3,2,N'王五' union all
select 4,2,N'王六' union all
select 5,2,N'王七'
GO
if not object_id(N'Tempdb..#展品') is null
drop table #展品
Go
Create table #展品([Id] int,[UserId] int,[Product] nvarchar(23))
Insert #展品
select 1,1,N'口罩' union all
select 2,1,N'无人机' union all
select 3,1,N'酒精' union all
select 4,1,N'蜡烛' union all
select 5,2,N'方便面' union all
select 6,2,N'白糖'
Go
--测试数据结束
SELECT CASE WHEN t2.rn=1 THEN ISNULL(t2.Company,t3.Company) ELSE '' END Company,t3.Name,t2.Product FROM (SELECT #展商.Id,Company,t1.UserId,t1.Product,t1.rn
FROM #展商
JOIN (SELECT *,ROW_NUMBER()OVER(PARTITION BY UserId ORDER BY GETDATE()) rn FROM #展品)t1
ON #展商.Id = t1.UserId )t2
FULL JOIN
(SELECT #展商.Id,Company,t1.UserId,t1.Name,t1.rn
FROM #展商
JOIN (SELECT *,ROW_NUMBER()OVER(PARTITION BY UserId ORDER BY GETDATE()) rn FROM #联系人)t1
ON #展商.Id = t1.UserId )t3
ON t3.Id = t2.UserId AND t3.rn = t2.rn
结果: