多结果子查询关联Accounts表获取最佳匹配账户号问题求助

解决方案 #

你原来的写法问题在于没有做两类账号的优先级互斥判断：当匹配公司的账号存在时，没有过滤掉counterparty为null的账号，所以会同时返回两类结果。以下是两种可行实现方案：

方案1：窗口函数优先级排序（适配所有支持窗口函数的主流数据库）

先给Accounts表的同portfolio记录按匹配优先级打标记，优先取匹配目标公司的，没有再取counterparty为null的：

INNER JOIN (
    SELECT * 
    FROM (
        SELECT 
            *,
            -- 优先级标记：匹配目标公司的记1，counterparty为null的记2
            ROW_NUMBER() OVER(
                PARTITION BY portfolio 
                ORDER BY CASE 
                    WHEN counterparty = position.cp THEN 1 
                    WHEN counterparty IS NULL THEN 2 
                    ELSE 3 
                END
            ) AS priority
        FROM accounts
    ) t
    WHERE priority <= 2 
    -- 确保如果有优先级1的记录，就只返回优先级1的，否则返回优先级2的
    AND NOT EXISTS (
        SELECT 1 
        FROM accounts a 
        WHERE a.portfolio = t.portfolio 
        AND a.counterparty = position.cp
        AND t.counterparty IS NULL
    )
) accounts ON 1=1 -- 此处可补充你其他的关联条件

方案2：简化的EXISTS判断写法（性能更优）

直接判断当前portfolio下是否存在匹配公司的账号，存在就取匹配的，否则取null的：

INNER JOIN accounts 
ON accounts.portfolio = position.portfolio
AND (
    -- 存在匹配公司的账号时，只取匹配的
    (EXISTS (
        SELECT 1 FROM accounts a 
        WHERE a.portfolio = position.portfolio 
        AND a.counterparty = position.cp
    ) AND accounts.counterparty = position.cp)
    -- 不存在时取counterparty为null的
    OR (NOT EXISTS (
        SELECT 1 FROM accounts a 
        WHERE a.portfolio = position.portfolio 
        AND a.counterparty = position.cp
    ) AND accounts.counterparty IS NULL)
)

如果你只需要返回对应的Account number值，也可以直接用COALESCE嵌套子查询实现，不需要写复杂JOIN逻辑：

SELECT
    COALESCE(
        (SELECT account_number FROM accounts WHERE portfolio = position.portfolio AND counterparty = position.cp),
        (SELECT account_number FROM accounts WHERE portfolio = position.portfolio AND counterparty IS NULL)
    ) AS matched_account_number
-- 你的主查询FROM逻辑

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