基于指定账户类型优先级筛选客户对应账户的技术需求

这是个很常见的分组优先级筛选需求，咱们直接用SQL来解决，思路清晰还容易扩展。

先明确需求 #

每个客户只保留优先级最高的账户类型，优先级顺序为：A1 > C1 > B1

测试数据准备 #

先把你提供的客户表和账户表转换成可执行的SQL语句，方便测试：

-- 创建客户表
CREATE TABLE customers (
    customer_ID INT,
    Name VARCHAR(50)
);

-- 插入客户数据
INSERT INTO customers VALUES
(101, 'Smith'),
(102, 'Williams'),
(103, 'Martin'),
(104, 'Jack');

-- 创建账户表
CREATE TABLE accounts (
    Account_ID INT,
    customer_ID INT,
    Account_Type VARCHAR(2)
);

-- 插入账户数据
INSERT INTO accounts VALUES
(201, 101, 'A1'),
(202, 101, 'B1'),
(203, 101, 'C1'),
(301, 102, 'B1'),
(302, 102, 'C1'),
(401, 103, 'A1'),
(402, 103, 'C1'),
(501, 104, 'B1');

核心解决方案（用窗口函数） #

用ROW_NUMBER()窗口函数给每个客户的账户按优先级排序，然后取排序第一的记录即可：

WITH ranked_accounts AS (
    SELECT 
        a.customer_ID,
        c.Name,
        a.Account_ID,
        a.Account_Type,
        -- 按优先级赋予排序值：A1最高，C1次之，B1最低
        ROW_NUMBER() OVER (
            PARTITION BY a.customer_ID 
            ORDER BY 
                CASE a.Account_Type 
                    WHEN 'A1' THEN 3 
                    WHEN 'C1' THEN 2 
                    WHEN 'B1' THEN 1 
                END DESC
        ) AS rn
    FROM accounts a
    JOIN customers c ON a.customer_ID = c.customer_ID
)
SELECT customer_ID, Name, Account_ID, Account_Type
FROM ranked_accounts
WHERE rn = 1;

筛选后的结果 #

思路解释 #

1. 窗口函数分组排序：用PARTITION BY customer_ID把数据按客户分组，每个客户内部按账户类型优先级排序（通过CASE语句把优先级转换成可排序的数值）
2. 取第一条记录：每个客户排序后的第一条记录（rn=1）就是优先级最高的账户
3. 关联客户表：把客户姓名也关联进来，结果更完整

如果后续需要调整优先级，只需要修改CASE语句里的数值即可，非常灵活～

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