博客

WHERE条件IN中的子查询如果最顶级带有group by会无效

sql查询的where条件中，IN一个子查询，子查询的其他条件都生效，但group by失效，然而单独执行子条件时结果却是正常的，如下：

SELECT
    t1.id,
    t1.aa,
    t1.bb
FROM
    table1 t1
WHERE
    t1.id IN (
        SELECT
            t1.id
        FROM
            table1 t1
        INNER JOIN table2 t2 ON t2.aa = t1.bb
        WHERE
            t1.cc = 1518867568232173570
        GROUP BY
            t2.dd
    )

多次尝试后发现，再嵌套一层子查询就会正常，或者INNER JOIN 子查询结果也可以：

SELECT
    t1.id,
    t1.aa,
    t1.bb
FROM
    table1 t1
WHERE
    t1.id IN (
        SELECT
            t3.id
        FROM
            (
                SELECT
                    t1.id
                FROM
                    table1 t1
                INNER JOIN table2 t2 ON t2.aa = t1.bb
                WHERE
                    t1.cc = 1518867568232173570
                GROUP BY
                    t2.dd
            ) t3
    )

将where id in 改成 INNER JOIN 子查询结果：

SELECT
    t1.id,
    t1.aa,
    t1.bb
FROM
    table1 t1
INNER JOIN (
    SELECT
        t1.id
    FROM
        table1 t1
    INNER JOIN table2 t2 ON t2.aa = t1.bb
    WHERE
        t1.cc = 1518867568232173570
    GROUP BY
        t2.dd
) t3 ON t3.id = t1.id