MySql Group by関数の正しい開き方
2648 ワード
グループ化関数を使用する場合、結果セットのフィルタリング、問題点、解決策
1.シーンの適用
時計が2枚ある
文章表(一対多伝言表)t_posts: oid, posts_name伝言表(多対一文章表)t_comment: oid, posts_id, msg_content, create_time
2.需要分析
各記事の最新回答内容を問い合わせる
3.SQL作成
現在2つの記事A,Bが存在すると仮定する(返信のデータベースへの記録の順序は下記と一致する)
上のsqlを実行すると、結果セットに大量のレコードが失われ、結果が間違っていることがわかります.
mysqlのhavingはgroupbyの後に実行される、すなわち、先にグループ化してフィルタリングしているが、2つ以上のメッセージレコードが存在するため、グループ化後の結果セットは、各メッセージの1つ目のみをグループ化後のレコード情報として取り、この場合having create_を使用するtime=max(create_time)では、max(create_time)は現在のパケットの最大時間である
は:2019-09-10と2019-09-09
上記のsqlでは結果セットが失われます
4.SQLの改造
グループ化後にマージされた重複結果セットがrownumの最小であることを知っているので、sqlを以下のように改造してもいいですか?
実行後もエラーが発生し、order byがgroup by&havingの後
後でhavingを使わずにorder byでグループ化後の結果を最適化してもいいのではないでしょうか.
having create_time = max(create_time)
結果セットが間違っていて、パケット結果に影響を与えることはできません.rownumの最小パケットに従って結果セットを繰り返し、ソートします.
5.究極の改造バージョン
order byはgroupbyに後で影響するしかないので、groupbyの前に結果セットを並べてからグループ化することができますか?
まだ使いにくいことに気づきましたが、サブクエリは確かに先にソートされました.
クエリー(explain)により、サブクエリーのorder byが最適化されていないことがわかりました.解決方法:サブクエリでlimit 9999999 を使用サブクエリでwhere条件を使用するcreate_time = (select max(create_time) from t_comment group by oid)
大功を成し遂げる
添付表文:
ダウンロード
追加の知識点:
mysql5.5とmysql 5.7のバージョンの違い:5.7+バージョン、limitを使用しないとgroupbyはorder byを最適化します
個人ブログをクリックして
1.シーンの適用
時計が2枚ある
文章表(一対多伝言表)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)
現在2つの記事A,Bが存在すると仮定する(返信のデータベースへの記録の順序は下記と一致する)
A : 2019-09-10
A : 2019-09-11
B : 2019-09-01
B : 2019-09-09
上のsqlを実行すると、結果セットに大量のレコードが失われ、結果が間違っていることがわかります.
mysqlのhavingはgroupbyの後に実行される、すなわち、先にグループ化してフィルタリングしているが、2つ以上のメッセージレコードが存在するため、グループ化後の結果セットは、各メッセージの1つ目のみをグループ化後のレコード情報として取り、この場合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はgroupbyに後で影響するしかないので、groupbyの前に結果セットを並べてからグループ化することができますか?
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が最適化されていないことがわかりました.解決方法:
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を使用しないとgroupbyはorder byを最適化します
個人ブログをクリックして