如何编写SQL查询仅返回colors列仅含指定值的行？

要实现仅返回colors列所有颜色都属于指定列表（如'red'、'blue'）的行，核心思路是验证拆分后的每一个颜色都不在指定列表之外，避免用IN（它只会匹配存在任一指定颜色的行）。以下是不同SQL方言的具体实现：

MySQL（8.0.19+版本） #

利用内置的STRING_SPLIT函数拆分逗号分隔值，结合NOT EXISTS筛选无非法颜色的行：

SELECT t.*
FROM your_table t
WHERE NOT EXISTS (
    SELECT 1
    FROM STRING_SPLIT(t.colors, ',') s
    WHERE TRIM(s.value) NOT IN ('red', 'blue')
)
AND t.colors IS NOT NULL AND t.colors != '';

注：TRIM用于处理颜色值前后可能存在的空格（比如'red, blue'这种带空格的情况），如果你的数据没有空格可以去掉。

MySQL（低于8.0.19版本） #

用递归CTE手动拆分逗号分隔值：

WITH RECURSIVE split_colors AS (
    SELECT 
        id,
        colors,
        TRIM(SUBSTRING_INDEX(colors, ',', 1)) AS color,
        TRIM(SUBSTRING(colors, LENGTH(SUBSTRING_INDEX(colors, ',', 1)) + 2)) AS remaining_colors
    FROM your_table
    WHERE colors IS NOT NULL AND colors != ''
    UNION ALL
    SELECT 
        id,
        colors,
        TRIM(SUBSTRING_INDEX(remaining_colors, ',', 1)) AS color,
        TRIM(SUBSTRING(remaining_colors, LENGTH(SUBSTRING_INDEX(remaining_colors, ',', 1)) + 2)) AS remaining_colors
    FROM split_colors
    WHERE remaining_colors != ''
)
SELECT DISTINCT t.*
FROM your_table t
LEFT JOIN split_colors sc ON t.id = sc.id
WHERE sc.color NOT IN ('red', 'blue')
GROUP BY t.id, t.colors -- 若需返回其他列，需将所有列加入GROUP BY，或开启ONLY_FULL_GROUP_BY例外
HAVING COUNT(sc.id) = 0;

PostgreSQL #

用STRING_TO_ARRAY和UNNEST拆分字符串：

SELECT t.*
FROM your_table t
WHERE NOT EXISTS (
    SELECT 1
    FROM UNNEST(STRING_TO_ARRAY(t.colors, ',')) AS color
    WHERE TRIM(color) NOT IN ('red', 'blue')
)
AND t.colors IS NOT NULL AND t.colors != '';

SQL Server #

使用STRING_SPLIT函数：

SELECT t.*
FROM your_table t
WHERE NOT EXISTS (
    SELECT 1
    FROM STRING_SPLIT(t.colors, ',') s
    WHERE TRIM(s.value) NOT IN ('red', 'blue')
)
AND t.colors IS NOT NULL AND t.colors != '';

额外注意事项 #

• 如果你的业务允许colors列为空或空字符串，可去掉末尾的AND t.colors IS NOT NULL AND t.colors != ''条件。
• 长期来看，逗号分隔的列不利于数据维护和查询性能，建议规范化表结构：新增一张颜色关联表，将每个颜色与主表id一一对应存储。

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