如何筛选MySQL中无匹配结果的product_id分组数据？

这里提供两种可行的实现方式，适用于不同MySQL版本：

方法1：兼容低版本MySQL（无窗口函数支持） #

先通过子查询找出所有没有任何匹配成功的product_id，再关联原查询结果获取这些分组的全部记录：

SELECT 
  main.product_id,
  main.serial,
  main.regex,
  main.result
FROM (
  -- 生成所有匹配结果的基础查询
  SELECT 
    t1.product_id,
    t1.serial,
    t2.regex,
    IF(t1.serial REGEXP t2.regex, "MATCHED", "NOT MATCHED") AS result
  FROM table1 t1
  INNER JOIN table2 t2 ON t1.product_id = t2.product_id
) main
INNER JOIN (
  -- 筛选出分组内无MATCHED的product_id
  SELECT product_id
  FROM (
    SELECT 
      t1.product_id,
      IF(t1.serial REGEXP t2.regex, "MATCHED", "NOT MATCHED") AS result
    FROM table1 t1
    INNER JOIN table2 t2 ON t1.product_id = t2.product_id
  ) temp
  GROUP BY product_id
  -- 统计分组内MATCHED的数量，等于0则说明该分组无匹配
  HAVING SUM(CASE WHEN result = 'MATCHED' THEN 1 ELSE 0 END) = 0
) target_pids ON main.product_id = target_pids.product_id

方法2：使用窗口函数（MySQL 8.0及以上版本） #

如果你的MySQL版本支持窗口函数，可以用更简洁的写法，一次查询完成统计和筛选：

SELECT 
  product_id,
  serial,
  regex,
  result
FROM (
  SELECT 
    t1.product_id,
    t1.serial,
    t2.regex,
    IF(t1.serial REGEXP t2.regex, "MATCHED", "NOT MATCHED") AS result,
    -- 按product_id分组，统计当前分组内匹配成功的总次数
    SUM(CASE WHEN t1.serial REGEXP t2.regex THEN 1 ELSE 0 END) OVER (PARTITION BY t1.product_id) AS matched_count
  FROM table1 t1
  INNER JOIN table2 t2 ON t1.product_id = t2.product_id
) temp
-- 只保留分组内无匹配的记录
WHERE matched_count = 0

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