詳解MySql Group by函數(shù)真正的打開方法！

在使用分組函數(shù)時(shí), 進(jìn)行結(jié)果集篩選, 遇到的一些問題以及解決辦法【推薦：mysql視頻教程】

1. 應(yīng)用場景

有兩張表

文章表(一對多留言表) t_posts: ? ? ?
oid, posts_name ? ?
留言表(多對一文章表) t_comment: ? ?
oid, posts_id, msg_content, create_time

2.需求分析

查詢每個(gè)文章的最新回復(fù)內(nèi)容

3.SQL編寫

select    tp.oid,   tp.posts_name,   tc.msg_content,   tc.create_time from t_posts tp  left join t_comment tc on tp.oid = tc.posts_id group by tp.oid having create_time = max(create_time)

假設(shè)現(xiàn)在有兩個(gè)文章A, B (回復(fù)的記錄在數(shù)據(jù)庫的順序與下述一致)

<p>A有一個(gè)回復(fù)記錄時(shí)間為: 2019-09-10   <br/>A有一個(gè)回復(fù)記錄時(shí)間為: 2019-09-11   <br/>B有一個(gè)回復(fù)記錄時(shí)間為: 2019-09-01   <br/>B有一個(gè)回復(fù)記錄時(shí)間為: 2019-09-09<br/></p>

運(yùn)行上面的sql, 會發(fā)現(xiàn)結(jié)果集丟失大量記錄, 并且結(jié)果是錯(cuò)誤的, 經(jīng)過查詢資料得知

mysql的 having 是在 group by 之后再執(zhí)行, 也就是說, 先分組, 在過濾, 但是因?yàn)榇嬖趦蓷l以上的留言記錄,
所以分組之后的結(jié)果集只會取每條留言的第一條作為分組之后的記錄信息, 這時(shí)如果使用having create_time = max(create_time)
那么, max(create_time) 為當(dāng)前分組的最大時(shí)間

為: 2019-09-10 和 2019-09-09

所以上述sql會丟失結(jié)果集

4.改造SQL

因?yàn)橹婪纸M之后合并的重復(fù)結(jié)果集為rownum最小的那條, 那么可不可以改造sql如下??

select    tp.oid,   tp.posts_name,   tc.msg_content,   tc.create_time from t_posts tp  left join t_comment tc on tp.oid = tc.posts_id group by tp.oid having create_time = max(create_time) -- 下面的是新增的sql order by tc.create_time desc

運(yùn)行之后發(fā)現(xiàn)依舊不好使, 證明order by 在group by & having 之后

后來想想可不可以 不用having, 直接用order by來優(yōu)化分組后的結(jié)果呢?

having create_time = max(create_time)

select    tp.oid,   tp.posts_name,   tc.msg_content,   tc.create_time from t_posts tp  left join t_comment tc on tp.oid = tc.posts_id group by tp.oid  order by tc.create_time desc

結(jié)果集錯(cuò)誤, 并不能影響分組結(jié)果, 依舊是按照rownum最小分組合并重復(fù)結(jié)果集, 然后在排序

5.終極改造版本

因?yàn)閛rder by 只能后影響group by, 那么是不是可以在group by 之前先把結(jié)果集排序一下, 然后再分組呢?

select * from (   select      tp.oid,     tp.posts_name,     tc.msg_content,     tc.create_time   from t_posts tp    left join t_comment tc on tp.oid = tc.posts_id   order by tc.create_time desc ) t  group by t.oid

發(fā)現(xiàn)還是不好使, 但是子查詢確實(shí)先排序了

經(jīng)查詢(explain), 發(fā)現(xiàn)子查詢的order by被優(yōu)化沒了, 解決辦法:

1. 在子查詢里使用limit 99999
2. 在子查詢里使用where條件, create_time = (select max(create_time) from t_comment group by oid)

select * from (   select      tp.oid,     tp.posts_name,     tc.msg_content,     tc.create_time   from t_posts tp    left join t_comment tc on tp.oid = tc.posts_id   order by tc.create_time desc limit 9999 ) t  group by t.oid

大功告成

附加知識點(diǎn):

mysql5.5 與 mysql 5.7 版本差異: 5.7+ 版本, 如果不使用 limit, group by 會把 order by 優(yōu)化掉