SQL Server中row_number() over partition by查询在租户'B'场景下结果不符合预期的问题排查

嘿，我看了你的查询语句和问题描述，发现核心问题出在没有关联FormSchema和RolesTable的Tenant列，再加上FULL OUTER JOIN的使用，导致不同租户的行被错误关联，干扰了ROW_NUMBER的排序逻辑，所以才会出现指定租户为'B'时结果不符合预期的情况。

下面给你修正后的方案，完全贴合你的需求：

修正后的查询语句 #

WITH CombinedData AS (
    -- 先拉取指定租户（这里是'B'）的有效Schema和Role关联数据
    SELECT 
        fs.SchemaId, 
        fs.SchemaName, 
        rt.RoleId, 
        rt.RoleName, 
        rt.Tenant,
        -- 给指定租户设最高优先级（1）
        Priority = 1
    FROM FormSchema fs
    INNER JOIN RolesTable rt 
        ON fs.SchemaId = rt.SchemaId 
        AND fs.Tenant = rt.Tenant -- 关键：确保Schema和Role属于同一租户
    WHERE fs.Tenant = 'B'

    UNION ALL

    -- 再拉取'All'租户的对应数据
    SELECT 
        fs.SchemaId, 
        fs.SchemaName, 
        rt.RoleId, 
        rt.RoleName, 
        rt.Tenant,
        -- 'All'租户优先级设为2，比指定租户低
        Priority = 2
    FROM FormSchema fs
    INNER JOIN RolesTable rt 
        ON fs.SchemaId = rt.SchemaId 
        AND fs.Tenant = rt.Tenant
    WHERE fs.Tenant = 'All'
),
RankedData AS (
    SELECT 
        *,
        -- 按SchemaName分组，优先级高的排前面
        rn = ROW_NUMBER() OVER (PARTITION BY SchemaName ORDER BY Priority ASC)
    FROM CombinedData
)
-- 只保留每个SchemaName下优先级最高的那一行
SELECT SchemaId, RoleId, RoleName, Tenant
FROM RankedData
WHERE rn = 1;

为什么这个方案能解决问题？ #

• 避免跨租户关联错误：通过fs.Tenant = rt.Tenant确保FormSchema和RolesTable的行属于同一个租户，不会出现“租户B的Schema关联到租户All的Role”这种混乱情况。
• 清晰的优先级逻辑：用Priority字段明确指定租户的优先级高于'All'，这样当同一个SchemaName同时存在于两个租户时，ROW_NUMBER会自动把指定租户的行排在前面，取rn=1就正好是你要的结果。
• 精准的数据范围：用UNION ALL分别拉取指定租户和'All'的数据，保证只处理符合条件的记录，不会引入多余的空行（原查询的FULL OUTER JOIN容易产生不必要的空值行，干扰排序）。

可选调整：保留无对应Role的Schema #

如果你需要包含FormSchema里有但RolesTable中没有对应Role的记录，只需要把上面的INNER JOIN改成LEFT JOIN就行，这样Role相关字段会显示为NULL，其他逻辑不变：

WITH CombinedData AS (
    SELECT 
        fs.SchemaId, 
        fs.SchemaName, 
        rt.RoleId, 
        rt.RoleName, 
        fs.Tenant,
        Priority = 1
    FROM FormSchema fs
    LEFT JOIN RolesTable rt 
        ON fs.SchemaId = rt.SchemaId 
        AND fs.Tenant = rt.Tenant
    WHERE fs.Tenant = 'B'

    UNION ALL

    SELECT 
        fs.SchemaId, 
        fs.SchemaName, 
        rt.RoleId, 
        rt.RoleName, 
        fs.Tenant,
        Priority = 2
    FROM FormSchema fs
    LEFT JOIN RolesTable rt 
        ON fs.SchemaId = rt.SchemaId 
        AND fs.Tenant = rt.Tenant
    WHERE fs.Tenant = 'All'
),
RankedData AS (
    SELECT 
        *,
        rn = ROW_NUMBER() OVER (PARTITION BY SchemaName ORDER BY Priority ASC)
    FROM CombinedData
)
SELECT SchemaId, RoleId, RoleName, Tenant
FROM RankedData
WHERE rn = 1;

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