一对多关联多表查询：解决DISTINCT仍返回重复行并获取目标结果

我来帮你搞定这个头疼的重复行问题！首先得搞清楚为什么直接用LEFT JOIN加DISTINCT不管用：当你把多个一对多关联的表直接JOIN时，每个子表的多条记录会和其他子表的记录产生笛卡尔积。比如Vendor1有2个联系人、2个决策者，光这两个表JOIN就会生成2×2=4条记录，这些记录的组合都是不同的，所以DISTINCT根本没法消除它们，但这显然不是你想要的结果。

那怎么得到你期望的那种“每个供应商的各子表记录一一对应”的结果呢？我们可以用窗口函数给每个子表的记录按供应商ID分组编号，然后JOIN的时候不仅匹配供应商ID，还匹配这个编号，这样就能避免交叉组合啦。

给你写好具体的SQL代码：

SELECT 
    a.Id AS ID,
    a.[Company Name] AS CompanyName,
    b.ContactName,
    c.DecisionMakerName,
    d.RegistrationNumber,
    e.Activity
FROM VendorRegistrations a
LEFT JOIN (
    SELECT 
        VendorRegistrationId,
        ContactName,
        ROW_NUMBER() OVER(PARTITION BY VendorRegistrationId ORDER BY Id) AS rn
    FROM VendorPointOfContacts
) b ON a.Id = b.VendorRegistrationId
LEFT JOIN (
    SELECT 
        VendorRegistrationId,
        DecisionMakerName,
        ROW_NUMBER() OVER(PARTITION BY VendorRegistrationId ORDER BY Id) AS rn
    FROM VendorKeyDecisionMakers
) c ON a.Id = c.VendorRegistrationId AND b.rn = c.rn
LEFT JOIN (
    SELECT 
        VendorRegistrationId,
        RegistrationNumber,
        ROW_NUMBER() OVER(PARTITION BY VendorRegistrationId ORDER BY Id) AS rn
    FROM VendorLegalRegistrations
) d ON a.Id = d.VendorRegistrationId AND b.rn = d.rn
LEFT JOIN (
    SELECT 
        VendorRegistrationId,
        Activity,
        ROW_NUMBER() OVER(PARTITION BY VendorRegistrationId ORDER BY Id) AS rn
    FROM VendorBusinessActivities
) e ON a.Id = e.VendorRegistrationId AND b.rn = e.rn
WHERE b.rn IS NOT NULL -- 要是想保留没有联系人的供应商，把这行删掉就行
ORDER BY a.Id, b.rn;

代码说明： #

• 每个子表的子查询里，用ROW_NUMBER()按VendorRegistrationId分组、按子表自身ID排序，给每条记录生成一个序号rn（比如Vendor1的联系人会是1、2，决策者也是1、2）。
• JOIN的时候，除了匹配主表和子表的供应商ID，还要匹配这个rn序号，这样每个供应商的第1条联系人就只会对应第1条决策者、第1条注册号、第1条业务活动，完美避免了笛卡尔积。
• 如果某个子表的记录数比其他子表少，对应的字段会显示NULL，你可以根据需求用COALESCE替换成默认值，不过你的期望结果里各子表记录数刚好一致，所以这个方案完全适配。

执行这个查询后，就能得到你想要的结果：

内容的提问来源于stack exchange，提问作者shivam