如何实现SQL查询：优先匹配指定tenant_id，无匹配用default且code唯一

简便的HQL实现方式 #

利用窗口函数ROW_NUMBER()给同一code下的记录按优先级排序，再筛选出每个code的第一条数据，完美匹配需求：

SELECT new Event(e.id, e.tenantId, e.name, e.code)
FROM (
    SELECT 
        e.*,
        ROW_NUMBER() OVER (
            PARTITION BY e.code 
            ORDER BY CASE WHEN e.tenantId = :tenantId THEN 1 ELSE 2 END
        ) AS rn
    FROM Event e
    WHERE e.tenantId IN (:tenantId, 'default')
) e
WHERE e.rn = 1

逻辑说明 #

• 分组排序：通过PARTITION BY e.code把数据按事件code分组，然后用CASE表达式给指定租户的记录标记优先级1，default租户的标记为2，这样同一code下指定租户的记录会排在最前面。
• 筛选结果：外层查询只保留每个分组里rn=1的记录，确保每个code仅返回一条优先级最高的记录。
• 适配所有场景：
  • 指定租户有对应code时，返回该租户的记录；
  • 指定租户无对应code时，自动 fallback 到default的记录；
  • 指定租户不存在时，所有code都返回default的记录，完全符合需求。

之前方案的问题分析 #

1. WHERE IN方案：只是把指定租户和default的记录都查出来，但同一code会有两条数据（如果两边都存在），没法实现去重。
2. GROUP BY方案：SQL规范要求GROUP BY之后，SELECT里的非聚合列必须出现在GROUP BY列表中，所以直接GROUP BY code会报错，因为id、tenant_id这些列不在GROUP BY里，这种方式根本拿不到完整的行数据。

内容的提问来源于stack exchange，提问作者Yehoshua S.