SQL关联balist与customer表如何按状态优先级去重查询

你可以根据你使用的数据库版本选择以下任意一种实现方式，两种方案返回的结果都完全匹配你给出的预期输出：

方式1：窗口函数实现（适配MySQL8.0+、PostgreSQL、SQL Server等所有现代数据库） #

SELECT id, ba, status, custid
FROM (
    SELECT 
        a.id,
        a.ba,
        b.status,
        b.custid,
        -- 按ba分组，A状态优先级高于I，排序后取每组第一条
        ROW_NUMBER() OVER (PARTITION BY a.ba ORDER BY CASE WHEN b.status = 'A' THEN 1 ELSE 2 END) AS rn
    FROM balist AS a
    INNER JOIN customer AS b ON a.ba = b.ba
    WHERE b.status IN ('A', 'I') -- 提前过滤只保留A、I状态，减少计算量
) t
WHERE rn = 1;

逻辑说明：按ba字段分组后，同组内A状态排在最前，每个分组只取排序后的第一行，自动实现「同ba有A就留A删I，无A则留I」的需求。

方式2：NOT EXISTS兼容实现（适配所有数据库版本，包括不支持窗口函数的低版本数据库） #

SELECT 
    a.id,
    a.ba,
    b.status,
    b.custid
FROM balist AS a
INNER JOIN customer AS b ON a.ba = b.ba
WHERE b.status IN ('A', 'I')
AND NOT EXISTS (
    -- 过滤掉当前行是I、且对应ba下存在A状态的记录
    SELECT 1 FROM customer b2 
    WHERE b2.ba = a.ba 
    AND b2.status = 'A'
    AND b.status = 'I'
);

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