LINQ中实现Outer Join并基于第二表RoleId条件筛选记录

嘿，我来帮你搞定这个SQL查询需求！根据你描述的场景——要关联Component和ComponentRights表，同时对重复的ComponentId只保留RoleId=2的记录，这里有两种实用的解决方案：

这种方法用窗口函数给每个ComponentId的记录排序，优先保留RoleId=2的条目，逻辑简洁且性能友好：

WITH RankedComponents AS (
    SELECT 
        c.ComponentId,
        c.ComponentName,
        cr.RoleId,
        cr.ComponentRightsID,
        -- 给同ComponentId的记录排序：RoleId=2的排第一，其他按RoleId顺序排
        ROW_NUMBER() OVER (
            PARTITION BY c.ComponentId 
            ORDER BY CASE WHEN cr.RoleId = 2 THEN 0 ELSE 1 END, cr.RoleId
        ) AS rn
    FROM Component c
    JOIN ComponentRights cr ON c.ComponentId = cr.ComponentId
)
SELECT ComponentId, ComponentName, RoleId, ComponentRightsID
FROM RankedComponents
WHERE rn = 1;

思路解释： #

1. 用CTE（公共表表达式）RankedComponents关联两张表，同时给每个ComponentId分组（PARTITION BY c.ComponentId）。
2. 用ROW_NUMBER()函数给组内记录排序：通过CASE语句把RoleId=2的记录标记为0，其他标记为1，这样排序后RoleId=2的记录会排在最前面，得到rn=1。
3. 最后筛选出rn=1的记录：对于有多个RoleId的ComponentId，只会保留RoleId=2的那条；对于只有单个RoleId的ComponentId，不管RoleId是不是2，都会保留唯一的那条。

如果你更习惯直观的条件判断，可以用子查询先找出存在重复RoleId的ComponentId集合，再分情况查询：

SELECT c.ComponentId, c.ComponentName, cr.RoleId, cr.ComponentRightsID
FROM Component c
JOIN ComponentRights cr ON c.ComponentId = cr.ComponentId
WHERE 
    -- 情况1：ComponentId没有多个不同RoleId，直接保留所有记录
    cr.ComponentId NOT IN (
        SELECT ComponentId
        FROM ComponentRights
        GROUP BY ComponentId
        HAVING COUNT(DISTINCT RoleId) > 1
    )
    -- 情况2：ComponentId有多个不同RoleId，只保留RoleId=2的记录
    OR (cr.ComponentId IN (
        SELECT ComponentId
        FROM ComponentRights
        GROUP BY ComponentId
        HAVING COUNT(DISTINCT RoleId) > 1
    ) AND cr.RoleId = 2);

思路解释： #

1. 第一个子查询找出所有存在多个不同RoleId的ComponentId（通过GROUP BY和HAVING COUNT(DISTINCT RoleId) > 1判断）。
2. 查询条件分为两部分：
   • 不在上述集合中的ComponentId，保留所有关联的记录；
   • 在上述集合中的ComponentId，只保留RoleId=2的记录。

两种方法都能满足你的需求，窗口函数的方法在大数据量下通常性能更好，因为只需要扫描表一次；子查询的方法逻辑更直白，适合快速理解。

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