V2EX = way to explore
V2EX 是一个关于分享和探索的地方
Sign Up Now
For Existing Member Sign In
› MySQL 5.5 Community Server
› MySQL 5.6 Community Server
› Percona Configuration Wizard
› XtraBackup 搭建主从复制
Great Sites on MySQL
› Percona
› MySQL Performance Blog
› Severalnines
推荐管理工具
› Sequel Pro
› phpMyAdmin
推荐书目
› MySQL Cookbook
MySQL 相关项目
› MariaDB
› Drizzle
参考文档
› http://mysql-python.sourceforge.net/MySQLdb.html
MySQL 相关项目
› MariaDB
› Drizzle
参考文档
› http://mysql-python.sourceforge.net/MySQLdb.html
This topic created in 1837 days ago, the information mentioned may be changed or developed.
导入数据
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 Apr 26, 2021
```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 Apr 26, 2021
select min(id), shop_id from test group by shop_id
|
 |
3
2kCS5c0b0ITXE5k2 Apr 26, 2021
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 Apr 26, 2021
子查询里面只返回了 t.id 不符合 group 条件.
|
 |
5
garlics OP |
 |
6
johnj Apr 26, 2021
为啥要用 group 用了 group 又不出出现 group 后的字段 那是不对的
|
|
7
2kCS5c0b0ITXE5k2 Apr 26, 2021
@garlics 我看错了 忽略我那 2 条.
|
 |
8
xuanbg Apr 26, 2021
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 Apr 26, 2021
SELECT * FROM itest WHERE id IN (SELECT min(e.id) FROM itest e GROUP by e.shop_id );
|
|
10
liangjx Apr 26, 2021
min+group by 的意思是找出 group 中最小的那一项
|
 |
11
zibber Apr 26, 2021
我记得是 mysql 版本的问题 子查询里要加一个 limit 不然数据结果有问题
|
 |
12
wowo243 Apr 26, 2021
|
 |
13
sycxyc Apr 26, 2021
可能是优化器 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 May 30, 2021 via Android
5.8 及以上版本可以用开窗函数，用 shop_id 分区，id 升序，这样每个分区取第一条就行了
in 里套子查询。。。，执行效率不会拉胯吗？🙃 |
Select Language