SQL需求：获取每位下单用户订购量最高的Top3啤酒商品

嘿，这个校园啤酒电商项目的需求很明确嘛！要拿到每个有下单记录的用户订购量最高的Top3商品，确实需要关联这四张表，核心是按用户分组后对商品的总订购量排序取前3。不同数据库的实现方式略有差异，我给你分情况说清楚：

MySQL 8.0+ 及以上版本（推荐用窗口函数） #

这是最简洁高效的写法，利用窗口函数ROW_NUMBER()来给每个用户的商品按订购量排序：

WITH user_product_quantities AS (
    SELECT
        u.username,
        p.id AS productId,
        p.name,
        SUM(od.quantity) AS total_quantity,
        -- 按用户分组，总订购量降序排序，给每个商品编号
        ROW_NUMBER() OVER (PARTITION BY u.id ORDER BY SUM(od.quantity) DESC) AS rn
    FROM users u
    -- 关联订单表，只取有下单记录的用户
    JOIN orders o ON u.id = o.user_id
    -- 关联订单详情表
    JOIN order_detail od ON o.id = od.order_id
    -- 关联商品表
    JOIN products p ON od.product_id = p.id
    -- 按用户+商品分组，计算每个用户每个商品的总订购量
    GROUP BY u.id, u.username, p.id, p.name
)
-- 筛选每个用户的前3名商品
SELECT username, productId, name, total_quantity
FROM user_product_quantities
WHERE rn <= 3
ORDER BY username, rn;

关键说明： #

• 用SUM(od.quantity)计算用户对某商品的总订购量，而不是单条订单的数量，毕竟用户可能多次下单同一款啤酒
• PARTITION BY u.id是按用户分组（用用户ID而不是用户名，避免重名用户的问题），ORDER BY SUM(od.quantity) DESC确保订购量高的商品排在前面
• 如果想要允许并列排名（比如两个商品订购量相同都算Top3），可以把ROW_NUMBER()换成RANK()或DENSE_RANK()，前者会跳过并列后的序号，后者不会

MySQL 5.x 版本（不支持窗口函数） #

如果你的MySQL版本比较老，只能用变量来实现排名：

SELECT
    username,
    productId,
    name,
    total_quantity
FROM (
    SELECT
        u.username,
        p.id AS productId,
        p.name,
        SUM(od.quantity) AS total_quantity,
        -- 用变量跟踪当前用户，重置排名序号
        @rn := IF(@current_user = u.id, @rn + 1, 1) AS rn,
        @current_user := u.id
    FROM users u
    JOIN orders o ON u.id = o.user_id
    JOIN order_detail od ON o.id = od.order_id
    JOIN products p ON od.product_id = p.id
    -- 初始化变量
    CROSS JOIN (SELECT @current_user := 0, @rn := 0) vars
    GROUP BY u.id, u.username, p.id, p.name
    -- 必须先按用户排序，再按订购量降序
    ORDER BY u.id, total_quantity DESC
) ranked
WHERE rn <= 3
ORDER BY username, rn;

PostgreSQL 版本 #

PostgreSQL的窗口函数用法和MySQL 8.0+基本一致，直接用下面的SQL即可：

WITH user_product_quantities AS (
    SELECT
        u.username,
        p.id AS productId,
        p.name,
        SUM(od.quantity) AS total_quantity,
        ROW_NUMBER() OVER (PARTITION BY u.id ORDER BY SUM(od.quantity) DESC) AS rn
    FROM users u
    INNER JOIN orders o ON u.id = o.user_id
    INNER JOIN order_detail od ON o.id = od.order_id
    INNER JOIN products p ON od.product_id = p.id
    GROUP BY u.id, u.username, p.id, p.name
)
SELECT username, productId, name, total_quantity
FROM user_product_quantities
WHERE rn <= 3
ORDER BY username, rn;

最后提醒： #

请确保你的表关联字段和实际数据库一致（比如orders.user_id对应users.id，order_detail.product_id对应products.id），如果字段名不一样，记得调整SQL里的关联条件哦！

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