SQL 查询 row_number() over partition by query result 在某些情况下返回错误

Question

我有两个 SQL Server 表:FormSchema 列

SchemaId, SchemaName, Tenant

RoleTable 列

SchemaId, RoleId, RoleName, Tenant

预期结果(包括以下三个条件):

在租户“A”中存在单个 SchemaName 的结果中选择行

在租户“全部”中存在单个 SchemaName 的结果中选择行

如果租户“A”和“全部”中存在重复/相同的 SchemaName，则选择结果中属于租户“A”的行。

询问:

select * 
from 
    (select 
         fs1.schemaid, fs1.schemaName, ar1.roleId, ar1.roleName, ar1.tenant,
         rn = row_number() over (partition by fs1.schemaName
                                 order by case when ar1.tenant = 'ALL' then 2 else 1 end, ar1.tenant)
     from   
         RolesTable ar1
     full outer join 
         FormSchema fs1 on ar1.SchemaId = fs1.SchemaId) as t3
where 
    rn = 1
    and Tenant in ('B', 'All')

请参阅 db fiddle 以获取表、记录以及预期和实际结果。
我在将租户提供为“B”预期结果时遇到的问题是错误的，但如果我使用“A”预期结果是正确的。
'B' 的预期结果是错误的**，因为它不满足以下条件:

如果租户 'B' 和 'All' 中都存在重复/相同的 SchemaName，则选择结果中属于租户 'B' 的行。架构名称“汽车”。所以查询应该在结果 'B' 'Car' 行中选择。

'B' 的预期结果:

SchemaId   RoleId                                   RoleName    Tenant
--------------------------------------------------------------------
'664'      '40ecca83-7fd9-4d63-9f56-c7a48442d844'   '#Test-1'   'B'
'456'      '40ecca83-7fd9-4d63-9f56-c7a48442d844'   '#Test-1'   'B'

提前致谢。

Answer 1

您应该移动 Tenant 的条件从子查询内的外部查询:

select * 
from (
  select fs1.schemaid, fs1.schemaName, ar1.roleId, ar1.roleName, ar1.tenant,
         row_number() over (
           partition by fs1.schemaName
           order by case when ar1.tenant = 'ALL' then 2 else 1 end, ar1.tenant
         ) rn
  from RolesTable ar1 full outer join FormSchema fs1
  on ar1.SchemaId= fs1.SchemaId
  where ar1.Tenant in (?, 'All')
) as t3
where rn = 1

替换 ?与 'A'或 'B' .
另外，不清楚您为什么要执行 FULL加入而不是 LEFT加入。
如果您想在两个表中的结果中出现不匹配的行，那么您可能应该使用 COALESCE() :

where coalesce(ar1.Tenant, fs1.Tenant) in (?, 'All')

见 demo .