V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
V2EX 提问指南
shubiao
V2EX  ›  问与答

[mysql]请问该 SQL 那个地方触发了临时表?

  •  
  •   shubiao · 2022-04-01 16:00:50 +08:00 · 733 次点击
    这是一个创建于 728 天前的主题,其中的信息可能已经有所发展或是发生改变。

    因为了解过一些 SQL 执行顺序,对该 SQL 有些不解的地方,为什么 having 后可用 select 中起的别名

    经 google 后得知触发了临时表,有懂得小伙伴烦请告知一下那个情况,如果还能能给解释一下,就更万分感谢了

    牛客网简单类题目: https://www.nowcoder.com/practice/ddbcedcd9600403296038ee44a172f2d

    select 
        university,
        avg(question_cnt) as avg_question_cnt,
        avg(answer_cnt) as avg_answer_cnt
    from
        user_profile
    group by 
        university 
    having 
        avg_question_cnt < 5
        or 
        avg_answer_cnt < 20
    

    8 种触发临时表的情况: https://www.w3cschool.cn/hjikt5/cir4pozt.html

    1 、UNION 查询;

    2 、用到 TEMPTABLE 算法或者是 UNION 查询中的视图;

    3 、ORDER BY 和 GROUP BY 的子句不一样时;

    4 、表连接中,ORDER BY 的列不是驱动表中的;

    5 、DISTINCT 查询并且加上 ORDER BY 时;

    6 、SQL 中用到 SQL_SMALL_RESULT 选项时;

    7 、FROM 中的子查询;

    8 、子查询或者 semi-join 时创建的表;

    第 1 条附言  ·  2022-04-02 14:32:01 +08:00
    结贴: https://stackoverflow.com/questions/3841295/sql-using-alias-in-group-by
    结论:mysql 和 postgreSQL 特有的优化,别多想
    5 条回复    2022-04-01 21:14:54 +08:00
    shubiao
        1
    shubiao  
    OP
       2022-04-01 16:09:08 +08:00
    鄙人是一位优秀的八股文选手,看过《从根上了解 InnoDB 》,却没通篇看完《 SQL 必知必会》,最近在补 SQL 。
    可以往底层了解释,请不吝指教
    privatetan
        2
    privatetan  
       2022-04-01 16:34:03 +08:00
    答:group by 触发了临时表的创建;
    having 查询可改成 子查询 ,
    即:
    select * from (
    select
    university,
    avg(question_cnt) as avg_question_cnt,
    avg(answer_cnt) as avg_answer_cnt
    from
    user_profile
    group by
    university
    ) where
    avg_question_cnt < 5
    or
    avg_answer_cnt < 20)
    shubiao
        3
    shubiao  
    OP
       2022-04-01 17:05:31 +08:00
    @privatetan 我翻了一些文章都是来来回回直接列各种情况,而且还列不全,比如就没有说单独的 group by 就会触发临时表。 能给推荐些文章、书吗
    shubiao
        4
    shubiao  
    OP
       2022-04-01 17:10:53 +08:00
    上面 sql 有点瑕疵,正确写法
    SELECT
    *
    FROM
    ( SELECT university, avg( question_cnt ) AS avg_question_cnt, avg( answer_cnt ) AS avg_answer_cnt FROM user_profile GROUP BY university ) t1
    WHERE
    avg_question_cnt < 5
    OR avg_answer_cnt < 20
    shubiao
        5
    shubiao  
    OP
       2022-04-01 21:14:54 +08:00
    子查询也会触发内部临时表
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   我们的愿景   ·   实用小工具   ·   5321 人在线   最高记录 6543   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 30ms · UTC 09:04 · PVG 17:04 · LAX 02:04 · JFK 05:04
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.