group 関数を使用して結果セットをフィルタリングするときに発生するいくつかの問題と解決策 [推奨 : mysql ビデオ チュートリアル ]
1. アプリケーション シナリオ
2 つのテーブルがあります
Article テーブル (1 対多) messages テーブル) t_posts:
oid, photos_name
メッセージ テーブル (多対 1 の記事テーブル) 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 があるとします (データベース内の返信レコードの順序は次のとおりです)
<p>A有一个回复记录时间为: 2019-09-10 <br>A有一个回复记录时间为: 2019-09-11 <br>B有一个回复记录时间为: 2019-09-01 <br>B有一个回复记录时间为: 2019-09-09<br></p>
上記の SQL を実行すると、次のことがわかります。結果セットで多数のレコードが失われ、結果が間違っていることがわかりました。データをクエリした後、
mysql の Have は group by の後に実行されることがわかりました。つまり、グループ化が最初に実行されます。ただし、メッセージ レコードが 3 つ以上あるため、
so グループ化すると、後続の結果セットは、グループ化後のレコード情報として各メッセージの最初のメッセージのみを取得します。
の場合、max(create_time) は現在のグループ化の最大時間です。
は、2019-09-10 および 2019-09-09
したがって、上記の SQL は次のようになります。結果セットを失う
4. 変換 SQL
グループ化後にマージされた重複した結果セットは、行番号が最小のものであることがわかっているため、 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 が After group by & getting
Later にあることを証明しています。そこで考えたのですが、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
結果set エラーはグループ化の結果には影響しません。重複した結果セットは、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
それはまだ不可能であることがわかりました。これは機能しますが、確かにサブクエリが最初に並べ替えられます
クエリ (説明) 後、サブクエリの order by が最適化されていることを確認しました。解決策:
- サブクエリの制限 99999
- Use whereサブクエリの条件、create_time = (oid によって t_comment グループから max(create_time) を選択)
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
Done
追加の知識ポイント:
mysql5.5 と mysql 5.7 バージョンの違い: バージョン 5.7 では、limit が使用されていない場合、group by は order by