版本:5.7.28
在查询开发过程中发现在in条件中的子查询其他条件都生效,唯独group by失效,单独查询条件里面的子查询结果又是正常的,如下:
SELECT
t1.*
FROM
t1,
t2
WHERE
t1.id IN (
SELECT
b.id
FROM
( SELECT a.id, a.ver_group_code, ver FROM a WHERE a.org_id = '27d8cfe8b0454599a478d65e474a09e1' AND a.del_flag = 0 ORDER BY a.ver DESC LIMIT 1000 ) b
GROUP BY
b.id
)
AND t2.buz_id = t1.id
后来经过尝试发现如果再嵌套一层子查询就会正常:
SELECT
t1.*
FROM
t1,
t2
WHERE
t1.id IN (
SELECT c.id FROM(
SELECT
b.id
FROM
( SELECT a.id, a.ver_group_code FROM a WHERE a.org_id = '27d8cfe8b0454599a478d65e474a09e1' AND a.del_flag = 0 ORDER BY a.ver DESC LIMIT 1000 ) b
GROUP BY
b.id
) c
)
AND t2.buz_id = t1.id
具体的底层原因目前还未深究,后续有时间再补充,但是结论就是in 条件中的子查询如果最顶级带有group by会无效。有知道原因的大佬欢迎指点哦!