收银系统SQL查询问题：区分刷卡与现金销售统计

我来帮你搞定这个SQL问题！

问题出在你原来的子查询上——虽然主查询过滤了刷卡支付的记录，但子查询计算销量的时候，根本没关联对应的支付类型，所以它统计的还是所有支付方式下该商品的总销量，自然结果和原查询一致。

下面是修正后的方案：

刷卡支付销售统计 #

直接关联receipts和sales表，过滤出刷卡支付的记录后再统计销量：

SELECT 
    categories.name AS category,
    products.name,
    r.pID,
    SUM(r.quantity) AS quantity,
    products.price,
    products.cost
FROM receipts r
LEFT JOIN products ON r.pID = products.id
LEFT JOIN sales x ON r.oID = x.id
LEFT JOIN categories ON products.cID = categories.id
WHERE DATE(r.ts) = CURDATE() 
  AND x.card = 1  -- 只保留刷卡支付的订单
GROUP BY categories.name, products.name, r.pID, products.price, products.cost
ORDER BY categories.name;

现金支付销售统计 #

把上面的x.card = 1改成x.card = 0就行：

SELECT 
    categories.name AS category,
    products.name,
    r.pID,
    SUM(r.quantity) AS quantity,
    products.price,
    products.cost
FROM receipts r
LEFT JOIN products ON r.pID = products.id
LEFT JOIN sales x ON r.oID = x.id
LEFT JOIN categories ON products.cID = categories.id
WHERE DATE(r.ts) = CURDATE() 
  AND x.card = 0  -- 只保留现金支付的订单
GROUP BY categories.name, products.name, r.pID, products.price, products.cost
ORDER BY categories.name;

额外小技巧：一个查询同时看两种支付类型 #

如果想在一个表格里同时看到每个商品的刷卡和现金销量，可以用条件聚合，省得跑两次查询：

SELECT 
    categories.name AS category,
    products.name,
    r.pID,
    SUM(CASE WHEN x.card = 1 THEN r.quantity ELSE 0 END) AS card_quantity,
    SUM(CASE WHEN x.card = 0 THEN r.quantity ELSE 0 END) AS cash_quantity,
    products.price,
    products.cost
FROM receipts r
LEFT JOIN products ON r.pID = products.id
LEFT JOIN sales x ON r.oID = x.id
LEFT JOIN categories ON products.cID = categories.id
WHERE DATE(r.ts) = CURDATE()
GROUP BY categories.name, products.name, r.pID, products.price, products.cost
ORDER BY categories.name;

原理很简单：我们直接在主查询里把receipts和对应的sales支付记录关联起来，过滤出指定支付类型的交易后，再用SUM()统计对应销量，这样就不会出现子查询“脱离”支付类型的问题了。另外注意GROUP BY里要包含所有非聚合字段，避免SQL严格模式下的报错。

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