SQL中WHERE NOT IN单列要求与子查询GROUP BY多列返回冲突问题

问题根因 #

NOT IN 语法要求右侧子查询只能返回单列结果集，你当前子查询返回了id和MIN(t1.rate)两列，因此触发报错。聚合计算的MIN(rate)仅作为分组逻辑的一部分，不需要放到子查询的返回列中。

另外需要注意：你原有的SELECT t1.id, MIN(t1.rate) FROM offers AS t1 GROUP BY t1.ean写法在严格SQL模式（如MySQL开启ONLY_FULL_GROUP_BY）下属于非法语法，因为非聚合列id不在GROUP BY子句中，返回的id不保证是对应最小rate的记录ID，更推荐使用窗口函数保证逻辑准确性。

解决方案 #

方案1：调整子查询返回列适配NOT IN（简单场景推荐） #

如果仅需要排除每个有库存的ean下rate最低的记录ID，直接调整子查询仅返回ID列即可：

支持QUALIFY语法的数据库（MySQL8.0+/PostgreSQL13+/Oracle等）

SELECT t2.id, t2.name, t2.ean 
FROM offers AS t2 
WHERE t2.id NOT IN (
    SELECT t1.id
    FROM offers AS t1
    WHERE t1.stock > 0
    QUALIFY ROW_NUMBER() OVER (PARTITION BY t1.ean ORDER BY t1.rate ASC) = 1
);

不支持QUALIFY语法的数据库

SELECT t2.id, t2.name, t2.ean 
FROM offers AS t2 
WHERE t2.id NOT IN (
    SELECT tmp.id
    FROM (
        SELECT 
            t1.id,
            ROW_NUMBER() OVER (PARTITION BY t1.ean ORDER BY t1.rate ASC) AS rn
        FROM offers AS t1
        WHERE t1.stock > 0
    ) tmp
    WHERE tmp.rn = 1
);

方案2：用LEFT JOIN实现排除逻辑（需要用到子查询多字段的场景推荐） #

如果你后续需要用到MIN(rate)的计算结果，可以用LEFT JOIN代替NOT IN，无需限制子查询返回列数：

SELECT t2.id, t2.name, t2.ean
FROM offers AS t2
LEFT JOIN (
    SELECT 
        t1.id AS min_rate_id,
        MIN(t1.rate) AS min_rate
    FROM offers AS t1
    WHERE t1.stock > 0
    GROUP BY t1.ean, t1.id
) AS t_min ON t2.id = t_min.min_rate_id
WHERE t_min.min_rate_id IS NULL;

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