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)在有兩個文章A, B (回復(fù)的記錄在數(shù)據(jù)庫的順序與下述一致)
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 運行之后發(fā)現(xiàn)依舊不好使, 證明order by 在group by & having 之后
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é)果集錯誤, 并不能影響分組結(jié)果, 依舊是按照rownum最小分組合并重復(fù)結(jié)果集, 然后在排序
5.終極改造版本
因為order 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)還是不好使, 但是子查詢確實先排序了
在子查詢里使用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 優(yōu)化掉
關(guān)于“MySql Group by函數(shù)怎么正確使用”這篇文章的內(nèi)容就介紹到這里，感謝各位的閱讀！
