SQL实现：判断每行Lunch列是否包含Breakfast列关键词并返回布尔值

核心思路 #

把Breakfast列中的关键词拆分为单个条目，再检查Lunch列内容是否包含其中任意一个关键词，以此判断result字段的true/false值。

1. PostgreSQL 实现 #

利用regexp_split_to_table拆分关键词（假设关键词用逗号分隔），结合EXISTS子查询完成匹配：

SELECT
  Breakfast,
  Lunch,
  EXISTS (
    SELECT 1
    FROM regexp_split_to_table(Breakfast, ',') AS key
    WHERE Lunch LIKE '%' || trim(key) || '%'
  ) AS result
FROM Recipes;

说明：如果关键词用其他符号分隔，替换regexp_split_to_table的第二个参数即可；trim用于去除关键词前后的空格。

2. MySQL 实现 #

方法一：递归拆分+关联匹配

通过递归CTE拆分Breakfast的关键词，再关联判断Lunch是否包含：

WITH RECURSIVE split_keys AS (
  SELECT
    id,
    Breakfast,
    Lunch,
    SUBSTRING_INDEX(Breakfast, ',', 1) AS key_word,
    SUBSTRING(Breakfast, LENGTH(SUBSTRING_INDEX(Breakfast, ',', 1)) + 2) AS remaining
  FROM Recipes
  UNION ALL
  SELECT
    id,
    Breakfast,
    Lunch,
    SUBSTRING_INDEX(remaining, ',', 1) AS key_word,
    SUBSTRING(remaining, LENGTH(SUBSTRING_INDEX(remaining, ',', 1)) + 2) AS remaining
  FROM split_keys
  WHERE remaining != ''
)
SELECT DISTINCT
  r.Breakfast,
  r.Lunch,
  CASE WHEN sk.key_word IS NOT NULL THEN TRUE ELSE FALSE END AS result
FROM Recipes r
LEFT JOIN split_keys sk ON r.id = sk.id AND r.Lunch LIKE CONCAT('%', sk.key_word, '%');

方法二：正则表达式直接匹配

将Breakfast的关键词拼接成正则模式，用REGEXP匹配Lunch：

SELECT
  Breakfast,
  Lunch,
  Lunch REGEXP REPLACE(Breakfast, ',', '|') AS result
FROM Recipes;

说明：如果Breakfast包含.、*等正则特殊字符，需要先转义再拼接，避免匹配出错。

3. SQL Server 实现 #

使用STRING_SPLIT拆分关键词（仅支持2016及以上版本），通过EXISTS判断匹配：

SELECT
  Breakfast,
  Lunch,
  CASE
    WHEN EXISTS (
      SELECT 1
      FROM STRING_SPLIT(Breakfast, ',') AS key
      WHERE Lunch LIKE '%' + LTRIM(RTRIM(key.value)) + '%'
    ) THEN CAST(1 AS BIT)
    ELSE CAST(0 AS BIT)
  END AS result
FROM Recipes;

说明：旧版本SQL Server可自定义字符串拆分函数替代STRING_SPLIT。

注意事项 #

• 确保关键词的分隔符统一（如逗号、空格），根据实际数据调整拆分逻辑。
• 如果关键词包含%、_等SQL通配符，需用ESCAPE子句处理，避免干扰LIKE匹配逻辑。

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