基于多规则从表中匹配对应Account_Id的SQL实现疑问

需求回顾 #

现有表包含字段：Ent_Id、Ent_Entity、Account_Entity、Close_Date、Account_Id，需为每个Ent_Id匹配唯一Account_Id，规则如下：

• 规则1：若单个Ent_Id对应唯一Account_Id，直接选取；
• 规则2：若单个Ent_Id对应多个Account_Id：
  • 2a：优先筛选Ent_Entity = Account_Entity的记录，若结果唯一则选取；
  • 2b：若筛选后仍有多个，选取Close_Date最大的记录的Account_Id。

问题分析 #

你尝试的CASE语句属于行级字段判断，无法处理「前一规则筛选后仍有多个结果时触发下一规则」的分组级逻辑。这类多层级优先级筛选场景，用**窗口函数ROW_NUMBER()**是更合适的方案——它能在每个Ent_Id分组内，按规则优先级给记录排序，直接取最优的第一条。

实现代码 #

MySQL版本 #

WITH ranked_records AS (
    SELECT 
        Ent_Id,
        Account_Id,
        -- 按规则生成排序优先级：先匹配实体，再按Close_Date降序
        ROW_NUMBER() OVER (
            PARTITION BY Ent_Id 
            ORDER BY 
                CASE WHEN Ent_Entity = Account_Entity THEN 0 ELSE 1 END,
                STR_TO_DATE(Close_Date, '%d/%m/%Y') DESC
        ) AS rn
    FROM your_table_name
)
SELECT Ent_Id, Account_Id
FROM ranked_records
WHERE rn = 1;

SQL Server版本 #

WITH ranked_records AS (
    SELECT 
        Ent_Id,
        Account_Id,
        ROW_NUMBER() OVER (
            PARTITION BY Ent_Id 
            ORDER BY 
                CASE WHEN Ent_Entity = Account_Entity THEN 0 ELSE 1 END,
                CONVERT(DATE, Close_Date, 103) DESC -- 103对应DD/MM/YYYY格式
        ) AS rn
    FROM your_table_name
)
SELECT Ent_Id, Account_Id
FROM ranked_records
WHERE rn = 1;

逻辑说明 #

1. PARTITION BY Ent_Id：将数据按Ent_Id分组，每个组内独立处理；
2. ORDER BY子句：
   • 用CASE把Ent_Entity = Account_Entity的记录排在最前面（0 < 1），对应规则2a；
   • 同一优先级内按Close_Date降序排序，对应规则2b；
3. ROW_NUMBER()给每个组内的记录按上述规则编号，编号为1的就是符合要求的记录；
4. 最后筛选出rn = 1的记录，得到每个Ent_Id匹配的唯一Account_Id。

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