SQL Server复杂LEFT JOIN查询需求：双表关联并按owner过滤结果

嘿，针对你要实现的这个LEFT JOIN逻辑，我给你准备了两种可行的方案，都能精准满足你的要求：当people对应的places有非空owner记录时只返回那条，没有的话就返回所有匹配的places记录。

方案一：用NOT EXISTS实现（直观易懂） #

这个方案逻辑直接，适合快速理解和调试：

SELECT 
    p.name,
    p.place,
    p.pid AS people_pid,
    pl.pid AS places_pid,
    pl.owner,
    pl.address
FROM people p
LEFT JOIN places pl ON p.pid = pl.pid
WHERE 
    -- 优先保留owner非空的记录
    pl.owner IS NOT NULL
    -- 要是当前pid没有任何非空owner的记录，就保留所有匹配项
    OR NOT EXISTS (
        SELECT 1 
        FROM places pl_sub 
        WHERE pl_sub.pid = p.pid 
        AND pl_sub.owner IS NOT NULL
    )
ORDER BY p.name, pl.address;

逻辑说明： #

• 第一部分条件pl.owner IS NOT NULL会直接筛选出所有owner非空的关联记录；
• 第二部分用NOT EXISTS子查询判断：如果某个pid在places表里根本没有非空owner的记录，那就把这个pid对应的所有places记录都留下来。

方案二：用窗口函数实现（性能更优） #

如果你的数据量比较大，窗口函数的方式效率会更高，因为只需要扫描places表一次：

WITH ranked_places AS (
    SELECT 
        *,
        -- 给每个pid的记录排序：owner非空的排第1位，其余按地址排序
        ROW_NUMBER() OVER (
            PARTITION BY pid 
            ORDER BY CASE WHEN owner IS NOT NULL THEN 0 ELSE 1 END, address
        ) AS rn,
        -- 标记当前pid是否存在非空owner的记录
        MAX(CASE WHEN owner IS NOT NULL THEN 1 ELSE 0 END) OVER (PARTITION BY pid) AS has_non_null_owner
    FROM places
)
SELECT 
    p.name,
    p.place,
    p.pid AS people_pid,
    rp.pid AS places_pid,
    rp.owner,
    rp.address
FROM people p
LEFT JOIN ranked_places rp ON p.pid = rp.pid
WHERE 
    -- 有非空owner的pid，只取排序第一的那条（就是owner非空的那条）
    (rp.has_non_null_owner = 1 AND rp.rn = 1)
    -- 没有非空owner的pid，取所有关联记录
    OR rp.has_non_null_owner = 0
ORDER BY p.name, rp.address;

逻辑说明： #

1. 先通过CTE给places表的每条记录打两个标记：
   • has_non_null_owner：1表示该pid存在非空owner的记录，0表示不存在；
   • rn：给同一个pid下的记录排序，owner非空的记录会被排在最前面（序号为1）；
2. 关联people表后，根据标记筛选：有非空owner的只留第一条，没有的就全留。

验证结果 #

运行任意一个方案的查询，都会得到你需要的结果集：

Mr John    | place1 | 1 | 1 | 1    | address1
Miss Smith | place2 | 2 | 2 | null | address3
Miss Smith | place2 | 2 | 2 | null | address4
Miss Smith | place2 | 2 | 2 | null | address5

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