如何用SQL统计数组列中各字符串的出现次数并排序？

因为不同SQL数据库解析JSON数组的语法不同，以下是主流数据库的实现方案：

PostgreSQL #

假设你的表名为your_table，存储数组的列名为values_col（建议避免使用Values这类关键字作为列名）：

SELECT
  elem AS "Value",
  COUNT(*) AS "Count"
FROM
  your_table,
  json_array_elements_text(values_col::json) AS elem
GROUP BY
  elem
ORDER BY
  "Count" DESC;

先将varchar类型的列转换为JSON格式，再用json_array_elements_text把数组拆分为单行元素，最后分组统计并按出现次数降序排序。

MySQL 8.0+ #

同样假设表名your_table，列名values_col：

SELECT
  jt.elem AS `Value`,
  COUNT(*) AS `Count`
FROM
  your_table,
  JSON_TABLE(
    values_col,
    '$[*]' COLUMNS (elem VARCHAR(255) PATH '$')
  ) AS jt
GROUP BY
  jt.elem
ORDER BY
  `Count` DESC;

利用JSON_TABLE将JSON数组展开为表格结构，提取元素后分组统计并排序。

SQL Server #

假设表名your_table，列名values_col：

SELECT
  JSON_VALUE(value, '$') AS "Value",
  COUNT(*) AS "Count"
FROM
  your_table
CROSS APPLY
  OPENJSON(values_col)
GROUP BY
  JSON_VALUE(value, '$')
ORDER BY
  "Count" DESC;

通过CROSS APPLY OPENJSON拆分数组为单行数据，提取元素后完成统计和排序。

注意 #

如果你的数组字符串不是标准JSON格式（比如使用单引号包裹元素），需要先通过REPLACE函数替换引号，确保数据库能正确解析，例如REPLACE(values_col, '''', '"')。

内容的提问来源于stack exchange，提问作者Spencer Alexander Moritz