MySQL 复杂的 in 子查询语句会导致筛选失效？

[garlics]

导入数据

CREATE TABLE test (
	id INT UNSIGNED auto_increment PRIMARY KEY,
	shop_id INT (11) UNSIGNED NOT NULL
);
INSERT INTO test (id, shop_id) VALUES (1, 1);
INSERT INTO test (id, shop_id) VALUES (2, 2);
INSERT INTO test (id, shop_id) VALUES (3, 3);
INSERT INTO test (id, shop_id) VALUES (4, 2);

使用语句查询，目的是找出每个 shop_id 下第一个 id

SELECT * FROM test WHERE id IN ( SELECT `t`.`id` FROM ( SELECT e.* FROM test e ORDER BY e.id DESC ) t GROUP BY `t`.`shop_id` );

结果第四条记录也被查询了出来，而子查询语句如果单独拉出来执行，结果是 1,2,3 。这是为什么？

[fage108]

```sql
SELECT * FROM test WHERE id IN ( SELECT `t`.`id` FROM ( SELECT e.* FROM test e ORDER BY e.id DESC ) t GROUP BY `t`.`id` );
```
你试试这个

[7Qi7Qi]

select min(id), shop_id from test group by shop_id

[2kCS5c0b0ITXE5k2]

SELECT * FROM test WHERE id IN ( SELECT `t`.`id`, `t`.`shop_id` FROM ( SELECT e.* FROM test e ORDER BY e.id DESC ) t GROUP BY `t`.`shop_id` );

[2kCS5c0b0ITXE5k2]

子查询里面只返回了 t.id 不符合 group 条件.

[garlics]

@emeab #4 但是子查询有两个，就报错了
@fage108 #1 还是不行
@7Qi7Qi #2 需求不是查出最小的 id，只是为了复现，我简化了问题

[johnj]

为啥要用 group 用了 group 又不出出现 group 后的字段 那是不对的

[2kCS5c0b0ITXE5k2]

@garlics 我看错了 忽略我那 2 条.

[xuanbg]

SELECT `t`.`id` FROM ( SELECT e.* FROM test e ORDER BY e.id DESC ) t GROUP BY `t`.`shop_id`结果是不稳定的呀。改成：SELECT `shop_id`, min( `id`) as id FROM test GROUP BY `shop_id`

[liangjx]

SELECT * FROM itest WHERE id IN (SELECT min(e.id) FROM itest e GROUP by e.shop_id );

[liangjx]

min+group by 的意思是找出 group 中最小的那一项

[zibber]

我记得是 mysql 版本的问题 子查询里要加一个 limit 不然数据结果有问题

[wowo243]

https://mp.weixin.qq.com/s?src=11&timestamp=1619443444&ver=3032&signature=d*bRf5P-TVPBWFq9OX3Tef-jWPxBMT*uJ8R1KtxNxkqfGZm0Bgfzv3cSHUJjynp-wLIg-7i26qW4HqrZH6Nb91vdOlIN9YaKjZmOJFX95JBLDw7E7I6T93LCgs4Zhydi&new=1
网上随便搜了一个说明，mysql5.7 里要先排序后分组需要加 limit，否则会被优化

[sycxyc]

可能是优化器 BUG
加多一层 MariaDB10.5.8 结果正确

```sql
SELECT
*
FROM
test
WHERE
`id` IN (SELECT
*
FROM
(SELECT
`t`.`id`
FROM
(SELECT
e.*
FROM
test e
ORDER BY e.id) t
GROUP BY `t`.`shop_id`) AS tt);
```

[512357301]

5.8 及以上版本可以用开窗函数，用 shop_id 分区，id 升序，这样每个分区取第一条就行了
in 里套子查询。。。，执行效率不会拉胯吗？🙃