Oracle SQL如何统计VARCHAR数组字段中的去重值总数

你的List列存储的是标准JSON格式的数组字符串，分版本对应解法如下：

推荐方案（Oracle 12c R2及以上版本） #

直接用Oracle原生JSON解析函数处理，无格式解析错误问题，性能更好：

• 实现逻辑：
  • 用JSON_TABLE将每行的JSON数组拆分为单个元素的多行记录
  • 对所有拆出的元素做去重计数
• 对应SQL（假设你的表名为target_table）：

SELECT COUNT(DISTINCT item_val) AS distinct_total
FROM target_table,
JSON_TABLE(
  list,
  '$[*]' COLUMNS (item_val VARCHAR2(200) PATH '$')
);

针对你给出的样例数据，上述SQL执行返回结果为3，符合预期。

兼容方案（Oracle 11g及更早无JSON函数版本） #

通过正则替换+层级查询拆分字符串实现：

SELECT COUNT(DISTINCT item_val) AS distinct_total
FROM (
  SELECT TRIM(BOTH '"' FROM REGEXP_SUBSTR(
           REPLACE(REPLACE(list, '[', ''), ']', ''),
           '[^,]+',
           1,
           LEVEL
         )) AS item_val
  FROM target_table
  CONNECT BY LEVEL <= REGEXP_COUNT(list, ',') + 1
    AND PRIOR id = id
    AND PRIOR SYS_GUID() IS NOT NULL
);

注意：该方案依赖数组元素不包含逗号的前提，如果元素内容本身带逗号会出现拆分错误，优先使用JSON函数方案。

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