PHP+MySQL多表查询：如何在SELECT中计算累计积分减待扣减积分的剩余值

解决方法 #

你的原代码存在SQL注入风险，建议后续将直接拼接$_POST['card_number']的逻辑改为参数化预处理查询，避免数据库被恶意攻击。

要新增剩余积分字段，你可以通过子查询提前计算对应用户的总待扣减积分，再用总积分减去该值即可，注意没有待扣减记录时需要用COALESCE函数将空值转为0，避免计算结果为空。同时原查询遗漏了GROUP BY子句，在开启ONLY_FULL_GROUP_BY规则的数据库环境下会执行报错，修改后的完整查询语句如下：

SELECT 
    e.*, 
    SUM(u.points) AS points, 
    SUM(u.cost) AS cost,
    MAX(DATE_FORMAT(u.date_points, '%e/%c/%Y')) AS data,
    SUM(u.points) - COALESCE((
        SELECT SUM(points) 
        FROM `deducted points` AS d 
        WHERE d.id_customer = e.id_customer
    ), 0) AS remaining_points
FROM `customer` AS e 
INNER JOIN `points` AS u ON e.id_customer = u.id_customer 
WHERE e.card_number = '$id'
GROUP BY e.id_customer

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