如何使用纯SQL统计test表逗号分隔my_list字段的去重值数量

问题原因 #

你写的SQL是直接对my_list整字段去重计数，没有拆分字段内的逗号分隔元素，因此无法统计字段内部的去重元素数量。

实现思路 #

核心逻辑是先将每行的my_list字段按逗号拆分为独立元素行，对元素去重后再按id分组统计数量，空值特殊处理返回0。

不同数据库实现方案 #

1. MySQL 8.0+ 版本

WITH RECURSIVE split_cte AS (
    SELECT 
        id,
        my_list,
        1 AS start_pos,
        IF(LOCATE(',', my_list) > 0, LOCATE(',', my_list), LENGTH(my_list) + 1) AS end_pos
    FROM test
    UNION ALL
    SELECT 
        id,
        my_list,
        end_pos + 1 AS start_pos,
        IF(LOCATE(',', my_list, end_pos + 1) > 0, LOCATE(',', my_list, end_pos + 1), LENGTH(my_list) + 1) AS end_pos
    FROM split_cte
    WHERE start_pos <= LENGTH(my_list)
)
SELECT 
    t.id,
    COUNT(DISTINCT TRIM(SUBSTRING(t.my_list, s.start_pos, s.end_pos - s.start_pos))) AS my_count
FROM test t
LEFT JOIN split_cte s ON t.id = s.id
GROUP BY t.id;

2. PostgreSQL 版本

SELECT 
    id,
    COALESCE(COUNT(DISTINCT TRIM(elem)), 0) AS my_count
FROM test
LEFT JOIN LATERAL UNNEST(STRING_TO_ARRAY(my_list, ',')) elem ON TRUE
GROUP BY id;

3. Hive/Spark SQL 版本

SELECT 
    id,
    COALESCE(COUNT(DISTINCT TRIM(elem)), 0) AS my_count
FROM test
LATERAL VIEW OUTER EXPLODE(SPLIT(my_list, ', ')) tmp AS elem
GROUP BY id;

以上方案均兼容空值场景，返回结果完全匹配预期。

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