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

這篇文章要跟大家介紹MySql Group by 函數的正確開啟方式，希望對大家有幫助！

MySql Group by 函數的正確開啟方式

在使用分組函數時, 進行結果集篩選, 遇到的一些問題以及解決方案【推薦：mysql影片教學】

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 (回應的記錄在資料庫的順序與下述一致)

<p>A有一个回复记录时间为: 2019-09-10   <br>A有一个回复记录时间为: 2019-09-11   <br>B有一个回复记录时间为: 2019-09-01   <br>B有一个回复记录时间为: 2019-09-09<br></p>

運行上面的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被優化沒了, 解決辦法:

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

2. 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 最佳化掉

#

以上是詳解MySql Group by函數真正的開啟方法！的詳細內容。更多資訊請關注PHP中文網其他相關文章！