MySQL单表查询优化：获取当日记录及用户历史未付金额总和

方案一：关联子查询（适合小型数据集） #

直接在主查询中筛选当日记录，同时通过关联子查询计算每个用户的历史待付款总和：

SELECT 
    c1.ID,
    c1.username,
    c1.date,
    c1.price,
    COALESCE(
        (SELECT SUM(price) 
         FROM Commandes c2 
         WHERE c2.username = c1.username 
           AND STR_TO_DATE(c2.date, '%m/%d/%Y') < STR_TO_DATE('11/18/2022', '%m/%d/%Y')
        ), 0
    ) AS solde
FROM Commandes c1
WHERE STR_TO_DATE(c1.date, '%m/%d/%Y') = STR_TO_DATE('11/18/2022', '%m/%d/%Y');

• COALESCE用于处理用户无历史记录的情况，将NULL转为0，保证结果格式统一
• 子查询通过username关联主查询，仅计算当前用户的历史未结清金额

方案二：预聚合左连接（适合大型数据集） #

先预计算所有用户的历史待付款总和，再与当日记录关联，减少重复计算：

SELECT 
    c1.ID,
    c1.username,
    c1.date,
    c1.price,
    COALESCE(c2.solde_total, 0) AS solde
FROM Commandes c1
LEFT JOIN (
    SELECT 
        username,
        SUM(price) AS solde_total
    FROM Commandes
    WHERE STR_TO_DATE(date, '%m/%d/%Y') < STR_TO_DATE('11/18/2022', '%m/%d/%Y')
    GROUP BY username
) c2 ON c1.username = c2.username
WHERE STR_TO_DATE(c1.date, '%m/%d/%Y') = STR_TO_DATE('11/18/2022', '%m/%d/%Y');

• 子查询仅执行一次聚合计算，效率优于逐条子查询
• 左连接确保当日所有记录都能被返回，即使用户没有历史待付款

额外建议 #

如果date字段当前是字符串类型，建议修改为DATE类型，这样可以省去STR_TO_DATE的转换操作，既简化查询又提升性能：

ALTER TABLE Commandes MODIFY COLUMN date DATE;

之后查询可以直接用日期字面量比较，比如date < '2022-11-18'。

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