如何让非穷尽GROUP BY不返回随机值

我刚接触SQL不久，还请多多包涵。我的需求是这样的：我有两张表，其中product_table的数据如下：

mysql> SELECT * FROM product_table;
+----+---------------------------+--------------------------------------+
| id | product_name              | category_array                       |
+----+---------------------------+--------------------------------------+
|  1 | Hike boots - Breathable M | ["12341", "21342", "31243"]          |
|  2 | Tent throwable - XL       | ["1239999", "21342", "1239999"]      |
|  3 | Running boots pegasus - S | [ "12341",  "1239999", "31243"]      |
|  4 | Light Xtra bright - Wide  | ["12399999", "21342", "41234"]       |
|  5 | Medical kit               | ["12399999", "12388888", "12377777"] |
+----+---------------------------+--------------------------------------+

我想要按分类分组查询，但发现非穷尽GROUP BY会返回随机值，怎么才能让结果固定呢？

别担心，我来给你捋清楚解决思路：

首先，你的category_array是JSON数组格式，得先把里面的分类ID拆成单独的行，不然没法按单个分类分组。MySQL 8.0及以上版本可以用JSON_TABLE来做拆分：

SELECT 
    p.id,
    p.product_name,
    j.category_id
FROM product_table p
JOIN JSON_TABLE(
    p.category_array,
    '$[*]' COLUMNS(category_id VARCHAR(20) PATH '$')
) j;

这个查询会把每个产品的多个分类拆成一行一行的记录，比如第一行产品会拆成3条，对应三个分类ID。

接下来，要解决GROUP BY返回随机值的问题，核心是明确指定你要返回分组里的哪一行数据，而不是让数据库随机选。这里给你两种常用方案：

方案1：用窗口函数（推荐，灵活度高） #

比如你想获取每个分类下ID最小的产品信息，可以用ROW_NUMBER()窗口函数给每个分组的行排序，然后取排序第一的：

WITH split_categories AS (
    SELECT 
        p.id,
        p.product_name,
        j.category_id
    FROM product_table p
    JOIN JSON_TABLE(
        p.category_array,
        '$[*]' COLUMNS(category_id VARCHAR(20) PATH '$')
    ) j
),
ranked_products AS (
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY category_id ORDER BY id ASC) AS rn
    FROM split_categories
)
SELECT category_id, id, product_name
FROM ranked_products
WHERE rn = 1;

这里PARTITION BY category_id按分类分组，ORDER BY id ASC让每个分组里的行按ID从小到大排序，rn=1就取每个分类里的第一个产品，结果完全固定，不会随机。

方案2：用聚合函数适配旧版本MySQL #

如果你用的是不支持窗口函数的旧版MySQL，可以用聚合函数配合GROUP_CONCAT来实现：

SELECT 
    j.category_id,
    MIN(p.id) AS product_id,
    SUBSTRING_INDEX(GROUP_CONCAT(p.product_name ORDER BY p.id ASC), ',', 1) AS product_name
FROM product_table p
JOIN JSON_TABLE(
    p.category_array,
    '$[*]' COLUMNS(category_id VARCHAR(20) PATH '$')
) j
GROUP BY j.category_id;

GROUP_CONCAT会把每个分类下的产品名称按ID排序拼接成字符串，再用SUBSTRING_INDEX取第一个，这样也能得到固定的结果。

另外提一句，从规范角度来说，最好开启MySQL的ONLY_FULL_GROUP_BY模式（默认是开启的），它会强制你在GROUP BY里包含所有非聚合字段，或者用聚合函数处理这些字段，从根源上避免随机值的问题。

备注：内容来源于stack exchange，提问作者user25147200