Oracle数据库：如何获取单一客户ID关联的双状态多账户数据

原SQL的问题 #

你写的WHERE条件(account_status_code = 350 and account_status_code = 450)逻辑矛盾——单条账户记录的状态码不可能同时等于两个不同值，这是导致无结果的核心原因。

最简实现SQL #

方法1：子查询筛选目标客户ID

SELECT 
    a.account_number,
    c.client_id,
    a.account_status_code
FROM test_client c
JOIN test_client_account_relation r ON c.client_id = r.client_rltsn_id
JOIN test_account a ON r.client_account_id = a.account_id
WHERE c.client_id IN (
    -- 先筛选同时拥有350和450状态账户的客户ID
    SELECT r_inner.client_rltsn_id
    FROM test_client_account_relation r_inner
    JOIN test_account a_inner ON r_inner.client_account_id = a_inner.account_id
    WHERE a_inner.account_status_code IN (350, 450)
    GROUP BY r_inner.client_rltsn_id
    HAVING COUNT(DISTINCT a_inner.account_status_code) = 2
)
ORDER BY c.client_id, a.account_status_code;

方法2：窗口函数（适合大数据量场景）

SELECT 
    account_number,
    client_id,
    account_status_code
FROM (
    SELECT 
        a.account_number,
        c.client_id,
        a.account_status_code,
        -- 标记客户是否同时包含350和450
        MAX(CASE WHEN a.account_status_code = 350 THEN 1 ELSE 0 END) OVER (PARTITION BY c.client_id) AS has_350,
        MAX(CASE WHEN a.account_status_code = 450 THEN 1 ELSE 0 END) OVER (PARTITION BY c.client_id) AS has_450
    FROM test_client c
    JOIN test_client_account_relation r ON c.client_id = r.client_rltsn_id
    JOIN test_account a ON r.client_account_id = a.account_id
) t
WHERE has_350 = 1 AND has_450 = 1
ORDER BY client_id, account_status_code;

说明 #

• 两种方法都先定位同时拥有350和450状态账户的客户ID，再查询这些客户的所有账户信息，完全匹配你的预期输出要求。
• 方法1逻辑直观，适合中小数据量；方法2用窗口函数减少关联次数，性能更优，适合大数据量场景。

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