如何在SQL中实现根据用户自定义标签进行分组聚合_通过字符串拆分与关联-SQL-PHP中文网

JSON_TABLE可将逗号分隔字段转为行集，需先用REPLACE+CONCAT构造合法JSON数组格式（如'["a","b"]'），再通过JSON_TABLE('$[*]' COLUMNS(tag TEXT PATH '$'))展开并JOIN聚合。

如何在sql中实现根据用户自定义标签进行分组聚合_通过字符串拆分与关联

MySQL 8.0+ 用 `JSON_TABLE` 拆分逗号分隔标签并 JOIN 聚合

MySQL 原生不支持直接拆分字符串数组，但 8.0+ 的 JSON_TABLE 可以把逗号分隔的标签字段（如 "tag_a,tag_b,tag_c"）转成行集合，再和主表关联。关键在于先用 REPLACE 和 CONCAT 构造合法 JSON 数组格式。

常见错误是漏掉引号包裹：直接写 JSON_TABLE('["a","b"]', ...) 没问题，但若从字段拼接，必须手动加双引号且转义内部引号——否则解析失败报错 Invalid JSON text。

实操建议：

假设表 articles 有字段 tag_list VARCHAR(500)，值为 "news,tech,ai"
先转成 JSON 字符串：CONCAT('["', REPLACE(tag_list, ',', '","'), '"]')

再用 JSON_TABLE 展开：

SELECT a.id, jt.tag, COUNT(*) 
FROM articles a
JOIN JSON_TABLE(
  CONCAT('["', REPLACE(a.tag_list, ',', '","'), '"]'),
  '$[*]' COLUMNS (tag TEXT PATH '$')
) AS jt
GROUP BY jt.tag;

PostgreSQL 用 `string_to_array()` + `UNNEST()` 最简洁

PostgreSQL 天然支持数组类型，string_to_array() 把字符串转数组，UNNEST() 把数组炸成多行，无需 JSON 中转，性能更稳、语法更直白。

注意点：若 tag_list 可能为空或全空格，UNNEST(string_to_array(trim(tag_list), ',')) 会返回空结果集（不是 NULL 行），导致该记录完全丢失。需要加 CASE WHEN 或用 LEFT JOIN LATERAL 保主表行。

实操建议：

基础写法（忽略空值）：

SELECT tag, COUNT(*) 
FROM articles, UNNEST(string_to_array(tag_list, ',')) AS tag 
GROUP BY tag;

保空标签记录（用 LATERAL）：

SELECT COALESCE(t.tag, 'none') AS tag, COUNT(*) 
FROM articles a 
LEFT JOIN LATERAL UNNEST(
  NULLIF(string_to_array(TRIM(a.tag_list), ','), ARRAY[''])
) AS t(tag) ON true 
GROUP BY t.tag;

SQL Server 需警惕 `STRING_SPLIT()` 的排序不可靠

SQL Server 2016+ 的 STRING_SPLIT() 是最常用方案，但它返回结果**不保证顺序**，且默认无序号列。如果业务依赖原始标签顺序（比如“第一标签为主分类”），直接 GROUP BY 会丢失上下文。

另一个坑是：当输入为 NULL 时，STRING_SPLIT(NULL, ',') 返回空结果集，而非单行 NULL —— 这和多数人直觉相反，容易导致 LEFT JOIN 后数据量突降。

实操建议：

强制保序需加 ORDER BY 子句（但仅限在子查询中显式排序，函数本身不提供序号）
处理 NULL 输入：CROSS APPLY STRING_SPLIT(ISNULL(tag_list, ''), ',')，再用 WHERE value != '' 过滤空字符串

示例（安全写法）：

SELECT s.value AS tag, COUNT(*) 
FROM articles a 
CROSS APPLY STRING_SPLIT(ISNULL(a.tag_list, ''), ',') s 
WHERE NULLIF(TRIM(s.value), '') IS NOT NULL 
GROUP BY s.value;

跨数据库兼容写法：避免内置函数，用递归 CTE 手动拆分

如果必须兼容 MySQL 5.7、旧版 SQL Server 或某些 OLAP 引擎（如 StarRocks 不支持 STRING_SPLIT），只能退回到递归 CTE。但要注意：递归深度受 max_recursive_iterations 限制（MySQL 默认 1000），标签数超限时会中断。

核心逻辑是每次取第一个逗号前的子串，再截掉已处理部分，直到无逗号为止。性能比内置函数差一个数量级，只适合标签总数

实操建议：