SQLで「グループごとにｎ件出力する」というSELECT文を書く
皆様あけましておめでとうございます。本年もどうぞよろしくお願いいたします。
さて、今日はSQLを久しぶりにいじっていて詰まったことを書きたいと思います。
下のようなテーブルがあって、「SSIDごとにステータスがONの最新の結果をn件出力する」ということを実行しなければならない場面に遭遇しました。 例を出すと、下のようなテーブルから「ステータスがONの最新の結果を1件出力したい」とします。
| id | time | ssid | status |
|---|---|---|---|
| 1 | 12:00 | ssid1 | ON |
| 2 | 12:00 | ssid2 | ON |
| 3 | 12:00 | ssid3 | ON |
| 4 | 12:10 | ssid1 | ON |
| 5 | 12:10 | ssid2 | ON |
| 6 | 12:10 | ssid3 | ON |
| 7 | 12:20 | ssid1 | ON |
| 8 | 12:20 | ssid2 | OFF |
| 9 | 12:20 | ssid3 | ON |
すると、上のテーブルから下のような結果が出てくるのが理想になります。
| id | time | ssid | status |
|---|---|---|---|
| 7 | 12:20 | ssid1 | ON |
| 5 | 12:10 | ssid2 | ON |
| 9 | 12:20 | ssid3 | ON |
しかしこのような抽出は、group by句、where句などの単純なものではできません…
さて、どうするかと思って検索するとこのような記事に引っかかりました。
今回はこの記事を全力で参考にして解決しました。
解法：IN句を用いた副問い合わせ
これを見た瞬間「ああ、副問い合わせとかそんなものあったなあ」となりました…(←ダメな奴)
最初に示した結果を抽出するには以下のようなSQLを書きます。
select * from table as t1 where id in (select id from table as t2 where t1.ssid=t2.ssid and status='ON' order by id desc limit 1) order by ssid, id desc;
主問い合わせ文のwhere句にはid in の形で指定し、副問い合わせ文のwhere句の中でグループにしたいカラムを結合します(この場合は t1.ssid=t2.ssid )。
また、「ステータスがONの」などの他の条件や「N研抽出する」などのLIMIT句も副問い合わせ文の中に記述します。
あとは適宜並び替えなどをすれば完了です。
今日はここまで！
