在使用分組函數時, 進行結果集篩選, 遇到的一些問題以及解決方案【推薦：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) 為目前分組的最大時間
所以上述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
- 在子查詢裡使用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 最佳化掉#