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

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 。这是为什么？

14 条回复 • 2021-05-30 15:28:00 +08:00

fage108

2021-04-26 17:23:11 +08:00

```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

2021-04-26 17:24:47 +08:00

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

2kCS5c0b0ITXE5k2

2021-04-26 17:26:13 +08:00

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

2021-04-26 17:27:16 +08:00

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

garlics

2021-04-26 17:32:52 +08:00

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

johnj

2021-04-26 17:39:32 +08:00

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

2kCS5c0b0ITXE5k2

2021-04-26 17:39:32 +08:00

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

xuanbg

2021-04-26 17:46:10 +08:00

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

2021-04-26 18:11:15 +08:00

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

liangjx

2021-04-26 18:13:16 +08:00

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

zibber

2021-04-26 19:23:27 +08:00

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

wowo243

2021-04-26 21:28:15 +08:00

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

2021-04-26 22:58:50 +08:00

可能是优化器 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

2021-05-30 15:28:00 +08:00 via Android

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