MySQL查询需求：按发票前缀聚合取最新状态并求和金额

针对你需要实现的查询需求，我整理了具体的SQL实现方案，完全匹配你期望的结果：

需求拆解 #

首先明确核心需求点：

• 提取InvoiceNo的前缀（以/为分隔符，取左侧内容，比如ABC/1→ABC；无/的直接保留原内容）
• 按前缀分组，对Amt求和
• 分组状态规则：如果组内存在Pending状态，优先显示Pending；否则保留组内唯一状态
• 生成连续的新Id

实现SQL语句 #

-- 记得把your_table_name替换成你的实际表名
SELECT
    ROW_NUMBER() OVER (ORDER BY invoice_prefix) AS Id,
    invoice_prefix AS InvoiceNo,
    -- 优先显示Pending状态，否则取组内的唯一状态
    CASE
        WHEN SUM(CASE WHEN Status = 'Pending' THEN 1 ELSE 0 END) > 0 THEN 'Pending'
        ELSE MAX(Status)
    END AS Status,
    SUM(Amt) AS Amt
FROM (
    -- 子查询：提取每条记录的发票前缀
    SELECT
        SUBSTRING_INDEX(InvoiceNo, '/', 1) AS invoice_prefix,
        Status,
        Amt
    FROM your_table_name
) AS prefix_sub
GROUP BY invoice_prefix
ORDER BY Id;

代码细节解释 #

1. 前缀提取：用SUBSTRING_INDEX(InvoiceNo, '/', 1)函数处理发票号，该函数会自动识别第一个/并截取左侧内容；如果发票号不含/，则直接返回原字符串，完美适配XYZ这类情况。
2. 分组求和：按提取的前缀分组，通过SUM(Amt)计算每组的总金额，对应你需要的ABC组300、XYZ组100的结果。
3. 状态逻辑：嵌套CASE语句先统计组内Pending状态的数量，只要存在就显示Pending；对于没有Pending的组（比如XYZ），用MAX(Status)获取唯一状态即可。
4. 新Id生成：用ROW_NUMBER() OVER (ORDER BY invoice_prefix)生成连续的新Id，保证结果的Id是有序且连续的。

执行结果 #

运行上述SQL后，会得到完全符合你预期的输出：

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