如何用单条SQL查询跨类别获取不重复的Top排名商品？

需求说明 #

根据传入的类别ID列表，获取每个类别的Top排名商品（按hits降序），要求选中的商品唯一，需排除此前已选中的Top商品。

示例数据表 #

product_category表 #

+------------+-------------+
| product_id | category_id |
+------------+-------------+
|     1      |       1     |
|     2      |       1     |
|     3      |       1     |
|     4      |       1     |
|     1      |       2     |
|     2      |       2     |
|     3      |       2     |
|     4      |       2     |
+------------+-------------+

products表 #

+------------+-------------+
| product_id |     hits    |
+------------+-------------+
|     1      |      123    |
|     2      |      333    |
|     3      |      444    |
|     4      |      555    |
+------------+-------------+

示例预期结果 #

查询类别[1,2]的Top商品，预期输出：

+-------------+-------------+
| category_id |  product_id |
+-------------+-------------+
|     1       |      4      |
|     2       |      3      | -- 因为product_id=4已被前一个类别选中
+-------------+-------------+

当前问题 #

现有方案是逐个查询类别并排除已选商品，但会产生大量数据库请求，能否用单条SQL实现该操作？

解决方案（单条SQL实现） #

可以通过**递归CTE（公共表表达式）**实现，以下是兼容PostgreSQL、MySQL 8.0+等支持递归CTE的数据库的写法：

WITH RECURSIVE category_queue AS (
    -- 初始化：定义要查询的类别顺序，这里传入[1,2]，可替换为实际类别列表
    SELECT 
        category_id,
        ROW_NUMBER() OVER () AS seq
    FROM (VALUES (1), (2)) AS c(category_id)
),
selected_products AS (
    -- 递归起点：处理第一个类别，选hits最高的商品
    SELECT 
        c.category_id,
        p.product_id,
        p.hits,
        c.seq,
        ARRAY[p.product_id] AS used_products
    FROM category_queue c
    JOIN product_category pc ON c.category_id = pc.category_id
    JOIN products p ON pc.product_id = p.product_id
    WHERE c.seq = 1
    ORDER BY p.hits DESC
    LIMIT 1
    
    UNION ALL
    
    -- 递归步骤：处理后续类别，排除已选中商品，选当前类别hits最高的未被选中商品
    SELECT 
        c.category_id,
        p.product_id,
        p.hits,
        c.seq,
        sp.used_products || p.product_id AS used_products
    FROM category_queue c
    JOIN selected_products sp ON c.seq = sp.seq + 1
    JOIN product_category pc ON c.category_id = pc.category_id
    JOIN products p ON pc.product_id = p.product_id
    -- PostgreSQL用unnest，MySQL替换为JSON_CONTAINS(sp.used_products, CAST(p.product_id AS JSON))
    WHERE p.product_id NOT IN (SELECT unnest(sp.used_products))
    ORDER BY p.hits DESC
    LIMIT 1
)
SELECT category_id, product_id FROM selected_products ORDER BY seq;

逻辑说明 #

1. category_queue：定义要查询的类别及其处理顺序，确保按传入列表依次处理。
2. 递归起点：处理第一个类别，筛选该类别下hits最高的商品，同时记录已选中的商品ID。
3. 递归步骤：依次处理后续每个类别，排除之前已选中的商品，筛选当前类别下hits最高的未被选中商品，更新已选中商品列表。
4. 最终从递归结果中提取目标字段，按处理顺序输出。

注意事项 #

• 不同数据库对数组/JSON的处理语法有差异：PostgreSQL用unnest，MySQL用JSON_CONTAINS，需根据实际使用的数据库调整对应语句。
• 若某个类别下所有商品都已被之前的类别选中，该类别不会返回结果，可根据业务需求添加默认逻辑。

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