詳解MySql Group by函數真正的開啟方法！

在使用分組函數時, 進行結果集篩選, 遇到的一些問題以及解決辦法【推薦：】

1. 應用場景

有兩張表

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

2.需求分析

查詢每個文章的最新回覆內容

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)

假設現在有兩個文章A, B (回覆的記錄在資料庫的順序與下述一致)

A有一個回覆記錄時間為: 2019-09-10   
A有一個回覆記錄時間為: 2019-09-11   
B有一個回覆記錄時間為: 2019-09-01   
B有一個回覆記錄時間為: 2019-09-09

執行上面的sql, 會發現結果集丟失大量記錄, 並且結果是錯誤的, 經過查詢資料得知

mysql的 having 是在 group by 之後再執行, 也就是說, 先分組, 在過濾, 但是因為存在兩條以上的留言記錄,
所以分組之後的結果集只會取每條留言的第一條作為分組之後的記錄資訊, 這時如果使用having create_time = max(create_time)
那麼, max(create_time) 為當前分組的最大時間

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

所以上述sql會丟失結果集

4.改造SQL

因為知道分組之後合併的重複結果集為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

執行之後發現依舊不好使, 證明order by 在group by & having 之後

後來想想可不可以 不用having, 直接用order by來優化分組後的結果呢?

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

結果集錯誤, 並不能影響分組結果, 依舊是按照rownum最小分組合並重復結果集, 然後在排序

5.終極改造版本

因為order by 只能後影響group by, 那麼是不是可以在group by 之前先把結果集排序一下, 然後再分組呢?

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

發現還是不好使, 但是子查詢確實先排序了

經查詢(explain), 發現子查詢的order by被優化沒了, 解決辦法:

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

大功告成

附加知識點:

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