如何通过一次查询提取数据表中某列的所有唯一标签？

假设你的数据表名为your_table，以下是不同数据库下的实现方案：

MySQL #

使用递归CTE拆分字符串后去重：

WITH RECURSIVE split_tags AS (
    SELECT 
        SUBSTRING_INDEX(tags, ',', 1) AS tag,
        SUBSTRING(tags, LENGTH(SUBSTRING_INDEX(tags, ',', 1)) + 2) AS remaining_tags
    FROM your_table
    WHERE tags IS NOT NULL AND tags != ''
    UNION ALL
    SELECT 
        SUBSTRING_INDEX(remaining_tags, ',', 1) AS tag,
        SUBSTRING(remaining_tags, LENGTH(SUBSTRING_INDEX(remaining_tags, ',', 1)) + 2) AS remaining_tags
    FROM split_tags
    WHERE remaining_tags IS NOT NULL AND remaining_tags != ''
)
SELECT DISTINCT TRIM(tag) AS unique_tag
FROM split_tags;

PostgreSQL #

借助string_to_array和unnest函数拆分：

SELECT DISTINCT TRIM(unnest(string_to_array(tags, ','))) AS unique_tag
FROM your_table
WHERE tags IS NOT NULL AND tags != '';

SQL Server #

使用STRING_SPLIT函数拆分并去重：

SELECT DISTINCT TRIM(value) AS unique_tag
FROM your_table
CROSS APPLY STRING_SPLIT(tags, ',')
WHERE tags IS NOT NULL AND tags != '';

以上查询都会返回所有不重复的标签，顺序可通过添加ORDER BY unique_tag调整。

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