V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
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
garlics
V2EX  ›  MySQL

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

  •  1
     
  •   garlics · 2021-04-26 17:06:48 +08:00 · 2551 次点击
    这是一个创建于 1285 天前的主题,其中的信息可能已经有所发展或是发生改变。

    导入数据

    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
        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` );
    ```
    你试试这个
    7Qi7Qi
        2
    7Qi7Qi  
       2021-04-26 17:24:47 +08:00
    select min(id), shop_id from test group by shop_id
    2kCS5c0b0ITXE5k2
        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` );
    2kCS5c0b0ITXE5k2
        4
    2kCS5c0b0ITXE5k2  
       2021-04-26 17:27:16 +08:00
    子查询里面只返回了 t.id 不符合 group 条件.
    garlics
        5
    garlics  
    OP
       2021-04-26 17:32:52 +08:00
    @emeab #4 但是子查询有两个,就报错了
    @fage108 #1 还是不行
    @7Qi7Qi #2 需求不是查出最小的 id,只是为了复现,我简化了问题
    johnj
        6
    johnj  
       2021-04-26 17:39:32 +08:00
    为啥要用 group 用了 group 又不出出现 group 后的字段 那是不对的
    2kCS5c0b0ITXE5k2
        7
    2kCS5c0b0ITXE5k2  
       2021-04-26 17:39:32 +08:00
    @garlics 我看错了 忽略我那 2 条.
    xuanbg
        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`
    liangjx
        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 );
    liangjx
        10
    liangjx  
       2021-04-26 18:13:16 +08:00
    min+group by 的意思是找出 group 中最小的那一项
    zibber
        11
    zibber  
       2021-04-26 19:23:27 +08:00
    我记得是 mysql 版本的问题 子查询里要加一个 limit 不然数据结果有问题
    wowo243
        12
    wowo243  
       2021-04-26 21:28:15 +08:00
    sycxyc
        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);
    ```
    512357301
        14
    512357301  
       2021-05-30 15:28:00 +08:00 via Android
    5.8 及以上版本可以用开窗函数,用 shop_id 分区,id 升序,这样每个分区取第一条就行了
    in 里套子查询。。。,执行效率不会拉胯吗?🙃
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   实用小工具   ·   2705 人在线   最高记录 6679   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 26ms · UTC 08:34 · PVG 16:34 · LAX 01:34 · JFK 04:34
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.