SQL查询使用聚合函数时如何去除重复条目

查询状态为Active的会员，需遵循以下规则：

• 若会员有缴费（Payd为Checked）记录，取最后一次缴费的记录；
• 若从未缴费，取最后一次未缴费（Payd为Unchecked）的账单记录；
• 若从未有任何账单，显示LastBill为空、Payd为Unchecked的条目。

当前SQL因按ID, Payd分组，会产生重复条目（如ID=2的会员返回两条记录），需修改SQL实现需求，替代Excel+pandas去重的方案。

表结构及测试数据 #

tblMembers表 #

tblBills表 #

当前存在问题的SQL语句 #

SELECT tblMembers.ID, Bills.lastBill, Bills.Payd, tblMembers.MemberStatus
FROM tblMembers LEFT JOIN (select ID, Payd, max(Year) as lastBill from tblBills group by ID, Payd)  AS Bills ON tblMembers.ID= Bills.ID
WHERE tblMembers.MemberStatus='Active'

修正后的SQL方案 #

使用窗口函数ROW_NUMBER()给每个会员的账单记录按规则排序，直接取符合需求的唯一记录：

WITH RankedBills AS (
    SELECT 
        ID,
        Year AS lastBill,
        Payd,
        -- 排序规则：优先已缴费记录，再按年份倒序
        ROW_NUMBER() OVER (
            PARTITION BY ID 
            ORDER BY CASE WHEN Payd = 'Checked' THEN 0 ELSE 1 END, Year DESC
        ) AS rn
    FROM tblBills
)
SELECT 
    m.ID,
    COALESCE(rb.lastBill, NULL) AS lastBill,
    COALESCE(rb.Payd, 'Unchecked') AS Payd,
    m.MemberStatus
FROM tblMembers m
LEFT JOIN RankedBills rb ON m.ID = rb.ID AND rb.rn = 1
WHERE m.MemberStatus = 'Active';

逻辑说明 #

1. RankedBills CTE：给每个会员的账单记录编号，已缴费（Checked）记录优先级更高（排序值为0），同类型记录按年份倒序，确保目标记录被标记为rn=1；
2. 主查询：关联会员表和排序后的账单表，仅取rn=1的记录；对无账单的会员，用COALESCE补全Payd为Unchecked，lastBill为NULL；
3. 最终每个Active会员仅返回一条符合需求的记录，无需后续去重操作。

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