如何筛选未包含全部指定ownerId的gridConfigId？

你的核心问题是GROUP_CONCAT生成的字符串顺序不固定，导致直接对比字符串无法得到正确结果。以下是几种更可靠的解决方法：

方法一：通过统计匹配的ownerId数量筛选 #

目标ownerId列表共有14个唯一值，我们可以统计每个gridConfigId对应的匹配数量，若数量小于14则说明至少缺少一个目标ownerId。同时需要覆盖那些没有任何关联ownerId的gridConfigId：

-- 筛选匹配数量不足14的gridConfigId
SELECT gf.gridConfigId
FROM gridconfig_favourites gf
JOIN gridconfigs g ON g.id = gf.gridConfigId
WHERE g.type = 'asset' AND g.name = 'Photo Attributes'
  AND gf.ownerId IN (2,3,7,8,9,10,12,13,14,16,18,22,28,30)
GROUP BY gf.gridConfigId
HAVING COUNT(DISTINCT gf.ownerId) < 14

UNION ALL

-- 补充筛选无关联ownerId的gridConfigId
SELECT g.id AS gridConfigId
FROM gridconfigs g
WHERE g.type = 'asset' AND g.name = 'Photo Attributes'
  AND NOT EXISTS (
      SELECT 1 FROM gridconfig_favourites gf WHERE gf.gridConfigId = g.id
  );

方法二：用反证法排除完全匹配的记录 #

找出所有符合条件的gridConfigId，排除那些包含所有目标ownerId的记录（即只要有一个目标ownerId不存在，就保留该gridConfigId）：

SELECT g.id AS gridConfigId
FROM gridconfigs g
WHERE g.type = 'asset' AND g.name = 'Photo Attributes'
  AND NOT EXISTS (
      -- 检查是否存在任意一个目标ownerId未被该gridConfigId包含
      SELECT 1
      FROM (
          SELECT 2 AS ownerId UNION ALL
          SELECT 3 UNION ALL
          SELECT 7 UNION ALL
          SELECT 8 UNION ALL
          SELECT 9 UNION ALL
          SELECT 10 UNION ALL
          SELECT 12 UNION ALL
          SELECT 13 UNION ALL
          SELECT 14 UNION ALL
          SELECT 16 UNION ALL
          SELECT 18 UNION ALL
          SELECT 22 UNION ALL
          SELECT 28 UNION ALL
          SELECT 30
      ) AS target
      WHERE NOT EXISTS (
          SELECT 1
          FROM gridconfig_favourites gf
          WHERE gf.gridConfigId = g.id AND gf.ownerId = target.ownerId
      )
  );

原查询的修正（仅作参考） #

如果一定要用GROUP_CONCAT，需要在函数内部指定排序规则，确保生成的字符串顺序固定，同时处理无ownerId的情况：

SELECT 
   gridConfigId, GROUP_CONCAT(ownerId ORDER BY ownerId SEPARATOR ',') as userList
from 
   gridconfig_favourites gf
where 
   EXISTS (SELECT 1 FROM gridconfigs g WHERE g.id=gf.gridConfigId AND type='asset' AND name='Photo Attributes')
GROUP BY gridConfigId
HAVING
   userList != '2,3,7,8,9,10,12,13,14,16,18,22,28,30'
   OR userList IS NULL;

注意：这种方法仅在ownerId无重复、且均为数字类型时可靠，若存在重复值或字符串类型，统计数量的方法更健壮。

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