MySQL按两列分组失效：如何实现每个分类仅返回一个商品？

核心问题在于原SQL的分组逻辑是按单个商品（idprod）+分类，所以每个商品都会单独成组，无法实现「每个分类仅返回一个匹配度最高商品」的需求。要实现这个目标，需要先计算所有商品的标签匹配度，再在分类维度筛选出匹配度Top1的商品。

方案1：使用窗口函数（推荐，支持SQL标准的数据库） #

这是最简洁高效的实现方式，适用于MySQL 8.0+、PostgreSQL、SQL Server等支持窗口函数的数据库：

WITH product_tag_match AS (
    -- 第一步：计算每个商品的匹配标签数量
    SELECT 
        p.idprod,
        p.category,
        p.*, -- 保留商品所有字段
        COUNT(t.id) AS best
    FROM tags t
    INNER JOIN products p ON p.idprod = t.idprod
    WHERE t.short IN ('one', 'two', 'four') -- 简化IN条件替代OR
    GROUP BY p.idprod, p.category, p.* -- 按商品唯一标识分组
)
-- 第二步：每个分类取匹配度最高的商品
SELECT *
FROM (
    SELECT 
        *,
        -- 按分类分组，匹配度降序排序，每个分类内的商品排号
        ROW_NUMBER() OVER (PARTITION BY category ORDER BY best DESC) AS rn
    FROM product_tag_match
    WHERE best > 2 -- 保留匹配数大于2的商品
) ranked
WHERE rn = 1 -- 取每个分类的Top1
ORDER BY best DESC -- 按匹配度排序结果
LIMIT 8;

关键说明： #

• 用WITH子句先计算每个商品的匹配标签数，替代原SQL的直接分组逻辑
• ROW_NUMBER() OVER (PARTITION BY category ORDER BY best DESC)：按category分组，组内按匹配度best降序给每个商品分配序号，序号为1的就是该分类匹配度最高的商品
• 将原SQL的LEFT JOIN改为INNER JOIN：需求是获取有效商品，不需要无对应商品的标签数据，避免返回products字段为NULL的无效结果
• 用IN ('one','two','four')简化原有的多个OR条件，更简洁高效

方案2：兼容老版本MySQL（无窗口函数） #

如果你的数据库不支持窗口函数（比如MySQL 5.x），可以用关联子查询实现：

SELECT 
    p.*,
    COUNT(t.id) AS best
FROM tags t
INNER JOIN products p ON p.idprod = t.idprod
WHERE t.short IN ('one', 'two', 'four')
GROUP BY p.idprod, p.category, p.*
HAVING best > 2
AND best = (
    -- 子查询：获取当前分类下的最高匹配数
    SELECT MAX(best_sub)
    FROM (
        SELECT COUNT(t_sub.id) AS best_sub
        FROM tags t_sub
        INNER JOIN products p_sub ON p_sub.idprod = t_sub.idprod
        WHERE t_sub.short IN ('one', 'two', 'four')
        AND p_sub.category = p.category
        GROUP BY p_sub.idprod
    ) sub
)
ORDER BY best DESC
LIMIT 8;

关键说明： #

• 子查询先计算当前分类下所有商品的最高匹配数，外层筛选出匹配数等于该最大值的商品
• 如果同一个分类有多个商品匹配度相同且都是最高值，这个写法会返回所有符合的商品；如果需要唯一返回一个，可以在子查询中加额外排序条件（比如取idprod最小的），修改内层子查询为SELECT COUNT(t_sub.id) AS best_sub ... ORDER BY best_sub DESC, p_sub.idprod ASC LIMIT 1

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