如何编写SQL查询获取商店最畅销产品——解决WHERE子句无法使用聚合函数的问题

嘿，我来帮你搞定这个问题！你一开始的思路没问题——先统计每个产品的订单次数，再筛选出订单最多的那个，但确实踩了个常见的SQL坑：WHERE子句不能直接使用MAX()这类聚合函数，因为WHERE是在聚合计算之前过滤行的，而MAX()是基于聚合后的结果计算的，两者执行顺序不匹配。

下面给你几个实用的解决方案，你可以根据自己的需求选择：

方案1：用ORDER BY + LIMIT（适合只需要单个最畅销产品） #

如果你的场景中最多只有一个销量最高的产品，或者你只需要返回其中一个，这个方法最简单直接：

SELECT products.*, COUNT(cheque.product_id) AS countOfOrders
FROM products
JOIN products_to_orders AS cheque ON products.id = cheque.product_id
GROUP BY products.id
ORDER BY countOfOrders DESC
LIMIT 1;

原理是先按订单数降序排序，然后只取第一行，就是销量最高的那个。

方案2：用窗口函数RANK()/DENSE_RANK()（支持并列第一的情况） #

如果有多个产品销量并列第一，你想把它们都返回，那窗口函数就很合适：

WITH product_order_counts AS (
    SELECT 
        products.*, 
        COUNT(cheque.product_id) AS countOfOrders,
        -- 按订单数降序排名，并列的产品会得到相同的排名
        RANK() OVER (ORDER BY COUNT(cheque.product_id) DESC) AS order_rank
    FROM products
    JOIN products_to_orders AS cheque ON products.id = cheque.product_id
    GROUP BY products.id
)
SELECT *
FROM product_order_counts
WHERE order_rank = 1;

这里用RANK()会让并列的产品共享第一名，如果想用连续的排名（比如两个第一之后是第二而不是第三），可以换成DENSE_RANK()，效果差不多，都能拿到所有销量最高的产品。

方案3：子查询获取最大订单数，再用HAVING匹配（兼容老版本数据库） #

如果你的数据库不支持窗口函数（比如MySQL 5.x及更早版本），可以用这种方法：

SELECT products.*, COUNT(cheque.product_id) AS countOfOrders
FROM products
JOIN products_to_orders AS cheque ON products.id = cheque.product_id
GROUP BY products.id
HAVING COUNT(cheque.product_id) = (
    -- 先算出所有产品里的最大订单数
    SELECT MAX(order_count)
    FROM (
        SELECT COUNT(product_id) AS order_count
        FROM products_to_orders
        GROUP BY product_id
    ) AS sub
);

这里用HAVING子句来过滤聚合后的结果，因为HAVING是在GROUP BY之后执行的，可以和子查询得到的最大订单数做比较，从而筛选出所有销量等于最大值的产品。

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