导入数据
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 。这是为什么?
1
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` ); ``` 你试试这个 |
2
7Qi7Qi 2021-04-26 17:24:47 +08:00
select min(id), shop_id from test group by shop_id
|
3
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` );
|
4
2kCS5c0b0ITXE5k2 2021-04-26 17:27:16 +08:00
子查询里面只返回了 t.id 不符合 group 条件.
|
5
garlics OP |
6
johnj 2021-04-26 17:39:32 +08:00
为啥要用 group 用了 group 又不出出现 group 后的字段 那是不对的
|
7
2kCS5c0b0ITXE5k2 2021-04-26 17:39:32 +08:00
@garlics 我看错了 忽略我那 2 条.
|
8
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`
|
9
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 );
|
10
liangjx 2021-04-26 18:13:16 +08:00
min+group by 的意思是找出 group 中最小的那一项
|
11
zibber 2021-04-26 19:23:27 +08:00
我记得是 mysql 版本的问题 子查询里要加一个 limit 不然数据结果有问题
|
12
wowo243 2021-04-26 21:28:15 +08:00
|
13
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); ``` |
14
512357301 2021-05-30 15:28:00 +08:00 via Android
5.8 及以上版本可以用开窗函数,用 shop_id 分区,id 升序,这样每个分区取第一条就行了
in 里套子查询。。。,执行效率不会拉胯吗?🙃 |