MySQL关联product与purchases表查询购买次数Top10商品名称及次数

原语句错误原因 #

• 表名拼写错误：关联条件中将purchases表误写为purchase，会直接触发表不存在的报错
• 缺少核心统计逻辑：没有对商品的购买次数做聚合计算，无法得到购买次数结果
• 没有排序规则：未按照购买次数降序排序，直接取LIMIT 10无法匹配「购买次数最高的前10个」的需求
• 冗余查询逻辑：同时查询max(productname)和productname属于无意义写法，productid是product表主键，一个productid对应唯一的productname，无需额外做max聚合

正确实现语句 #

写法1（性能更优，先统计再关联） #

先对购买表做聚合统计、排序、取TOP10，再关联商品表取名称，适合数据量较大的场景：

SELECT
    p.productname,
    pc.purchase_count
FROM (
    -- 统计每个商品的购买次数，取TOP10
    SELECT
        fproductid,
        COUNT(*) AS purchase_count
    FROM purchases
    GROUP BY fproductid
    ORDER BY purchase_count DESC, fproductid ASC
    LIMIT 10
) pc
LEFT JOIN product p ON pc.fproductid = p.productid;

写法2（直接联表分组，逻辑更直观） #

适合数据量较小的场景，需要注意如果开启了MySQL的ONLY_FULL_GROUP_BY模式，GROUP BY需要带上所有非聚合的查询字段：

SELECT
    p.productname,
    COUNT(*) AS purchase_count
FROM product p
INNER JOIN purchases pu ON p.productid = pu.fproductid
GROUP BY p.productid, p.productname
ORDER BY purchase_count DESC
LIMIT 10;

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