如何在关联3张表的查询中统计商品关联的折扣数量？

错误原因 #

你写的COUNT(discounts.*)是无效语法，MySQL不支持在COUNT函数中使用表名.*的写法。

正确写法 #

要统计关联的折扣数量，有两种可靠的方式：

方式1：统计折扣表的主键字段 #

利用COUNT(discounts.id)，LEFT JOIN后无折扣的产品对应的discounts.id为NULL，COUNT会自动忽略NULL值，得到准确的关联折扣数：

SELECT products.id, products.title, products.sale_price,
    COUNT(discounts.id) AS discounts_count,
    GROUP_CONCAT(CONCAT(discounts.name, ': ', discounts.min_qty, ': ', discounts.max_qty, ': ', discounts.percent)) AS discount_info
FROM products
    LEFT JOIN discount_product ON discount_product.product_id = products.id
    LEFT JOIN discounts on discounts.id = discount_product.discount_id
WHERE products.status = 'A' AND products.discount_price_allowed = 1 AND products.in_stock = 1 AND products.stock_qty > 1
GROUP BY products.id, products.title, products.sale_price

方式2：统计中间关联表的主键 #

也可以统计discount_product.id，中间表的记录数就是产品关联的折扣数，效果和上面一致：

SELECT products.id, products.title, products.sale_price,
    COUNT(discount_product.id) AS discounts_count,
    GROUP_CONCAT(CONCAT(discounts.name, ': ', discounts.min_qty, ': ', discounts.max_qty, ': ', discounts.percent)) AS discount_info
FROM products
    LEFT JOIN discount_product ON discount_product.product_id = products.id
    LEFT JOIN discounts on discounts.id = discount_product.discount_id
WHERE products.status = 'A' AND products.discount_price_allowed = 1 AND products.in_stock = 1 AND products.stock_qty > 1
GROUP BY products.id, products.title, products.sale_price

注意事项 #

如果直接用COUNT(*)，会把没有关联折扣的产品统计为1（LEFT JOIN会保留主表所有行，即使关联表无匹配也会生成一行NULL记录），因此必须用COUNT某个关联表的非NULL字段来获取准确数量。

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