[MySQL]最新日付のデータのIDを抽出する
Read More
最新日付のデータのIDを抽出する。
下記のようなテーブルデータがあったとする。
> SELECT * FROM t_contact;
+----+-----------+---------------------+
| id | office_id | regist_date |
+----+-----------+---------------------+
| 1 | 5 | 2014-10-16 01:00:00 |
| 2 | 5 | 2014-10-16 02:00:00 |
| 3 | 6 | 2014-10-16 03:00:00 |
| 4 | 6 | 2014-10-16 04:00:00 |
| 5 | 7 | 2014-10-16 05:00:00 |
+----+-----------+---------------------+
これを、office_id別でregist_dateの最新日付のidを取得したい。
期待する結果↓
+----+-----------+---------------------+
| id | office_id | regist_date |
+----+-----------+---------------------+
| 2 | 5 | 2014-10-16 02:00:00 |
| 4 | 6 | 2014-10-16 04:00:00 |
| 5 | 7 | 2014-10-16 05:00:00 |
+----+-----------+---------------------+
このようなことをしたいときは以下のようにSQLを書く。
> SELECT id, office_id, regist_date FROM (SELECT * FROM t_contact ORDER BY regist_date DESC) AS temp1 GROUP BY office_id ORDER BY id;
以下は上記のSQL文を見やすいようにインデントを付けたもの↓
ポイントはFROM句の中でサブクエリを使っている点。
そのサブクエリでは何をしているかと言うとORDER BYを使ってテーブルの並び替えをしてるだけ。
この時点ではテーブルデータは以下のように逆に並び替えられている。
+----+-----------+---------------------+
| id | office_id | regist_date |
+----+-----------+---------------------+
| 5 | 7 | 2014-10-16 05:00:00 |
| 4 | 6 | 2014-10-16 04:00:00 |
| 3 | 6 | 2014-10-16 03:00:00 |
| 2 | 5 | 2014-10-16 02:00:00 |
| 1 | 5 | 2014-10-16 01:00:00 |
+----+-----------+---------------------+
次に、GROUP BYでoffice_idをグルーピングしている。
グルーピングすることによってoffice_idが同じ値のレコードは1レコードに集約されるのだが、
複数レコードが1行に集約される際先頭行のデータが利用される。
office_idが5のレコードだけで見てみると以下のようになる。
・グルーピング前
+----+-----------+---------------------+
| 2 | 5 | 2014-10-16 02:00:00 |
| 1 | 5 | 2014-10-16 01:00:00 |
+----+-----------+---------------------+
・グルーピング後
+----+-----------+---------------------+
| 2 | 5 | 2014-10-16 02:00:00 |
+----+-----------+---------------------+
あと注意すべき点は、サブクエリを使う際はテーブル名に別名をつけておくこと。
テーブルの別名はtemp1でもsubquery1でもなんでもよい。
テーブルに別名がないとSQLは動かないので必ず付ける必要がある。
あと別名を付ける場合は必ずAS句を使うようにすること。
このAS句は省略することもできるがSQL文が読みづらくなるので省略しない方が個人的にはお勧め。
最後に並び替えが逆になってしまっているので通常の並びに戻すためにORDER BYを使っている。
これで当初の希望する結果が返る。
・office_id別でregist_dateが最新レコードのみを取得した
+----+-----------+---------------------+
| id | office_id | regist_date |
+----+-----------+---------------------+
| 2 | 5 | 2014-10-16 02:00:00 |
| 4 | 6 | 2014-10-16 04:00:00 |
| 5 | 7 | 2014-10-16 05:00:00 |
+----+-----------+---------------------+
--
ちなみに先述したような結果を取り出すのにTEMPORARY TABLEを使っても同じことができる。
ただテンポラリテーブルは色々と制限があったりする。
ネストしすぎてSQLの可読性が下がるような場合を除いて、基本的に使わない方が良いのではないかと思っている。
--
最後に、一度グルーピングしてしまうと他の色々な操作がしづらくなる。
なので先ほどのSQL文をもう一階層ネストしてやると利用しやすくなる。
> SELECT * FROM (SELECT * FROM (SELECT * FROM t_contact ORDER BY regist_date DESC) AS temp1 GROUP BY office_id ORDER BY id) AS temp2 WHERE ...;
うん、可読性下がるねえ…
あーあと某サイトで見たけどMAX関数を使ったやり方はダメ。
結果がおかしくなる。
下記のようなテーブルデータがあったとする。
> SELECT * FROM t_contact;
+----+-----------+---------------------+
| id | office_id | regist_date |
+----+-----------+---------------------+
| 1 | 5 | 2014-10-16 01:00:00 |
| 2 | 5 | 2014-10-16 02:00:00 |
| 3 | 6 | 2014-10-16 03:00:00 |
| 4 | 6 | 2014-10-16 04:00:00 |
| 5 | 7 | 2014-10-16 05:00:00 |
+----+-----------+---------------------+
これを、office_id別でregist_dateの最新日付のidを取得したい。
期待する結果↓
+----+-----------+---------------------+
| id | office_id | regist_date |
+----+-----------+---------------------+
| 2 | 5 | 2014-10-16 02:00:00 |
| 4 | 6 | 2014-10-16 04:00:00 |
| 5 | 7 | 2014-10-16 05:00:00 |
+----+-----------+---------------------+
このようなことをしたいときは以下のようにSQLを書く。
> SELECT id, office_id, regist_date FROM (SELECT * FROM t_contact ORDER BY regist_date DESC) AS temp1 GROUP BY office_id ORDER BY id;
以下は上記のSQL文を見やすいようにインデントを付けたもの↓
SELECT id, office_id, regist_date FROM (SELECT * FROM t_contact ORDER BY regist_date DESC) AS temp1 GROUP BY office_id ORDER BY id;
ポイントはFROM句の中でサブクエリを使っている点。
そのサブクエリでは何をしているかと言うとORDER BYを使ってテーブルの並び替えをしてるだけ。
この時点ではテーブルデータは以下のように逆に並び替えられている。
+----+-----------+---------------------+
| id | office_id | regist_date |
+----+-----------+---------------------+
| 5 | 7 | 2014-10-16 05:00:00 |
| 4 | 6 | 2014-10-16 04:00:00 |
| 3 | 6 | 2014-10-16 03:00:00 |
| 2 | 5 | 2014-10-16 02:00:00 |
| 1 | 5 | 2014-10-16 01:00:00 |
+----+-----------+---------------------+
次に、GROUP BYでoffice_idをグルーピングしている。
グルーピングすることによってoffice_idが同じ値のレコードは1レコードに集約されるのだが、
複数レコードが1行に集約される際先頭行のデータが利用される。
office_idが5のレコードだけで見てみると以下のようになる。
・グルーピング前
+----+-----------+---------------------+
| 2 | 5 | 2014-10-16 02:00:00 |
| 1 | 5 | 2014-10-16 01:00:00 |
+----+-----------+---------------------+
・グルーピング後
+----+-----------+---------------------+
| 2 | 5 | 2014-10-16 02:00:00 |
+----+-----------+---------------------+
あと注意すべき点は、サブクエリを使う際はテーブル名に別名をつけておくこと。
テーブルの別名はtemp1でもsubquery1でもなんでもよい。
テーブルに別名がないとSQLは動かないので必ず付ける必要がある。
あと別名を付ける場合は必ずAS句を使うようにすること。
このAS句は省略することもできるがSQL文が読みづらくなるので省略しない方が個人的にはお勧め。
最後に並び替えが逆になってしまっているので通常の並びに戻すためにORDER BYを使っている。
これで当初の希望する結果が返る。
・office_id別でregist_dateが最新レコードのみを取得した
+----+-----------+---------------------+
| id | office_id | regist_date |
+----+-----------+---------------------+
| 2 | 5 | 2014-10-16 02:00:00 |
| 4 | 6 | 2014-10-16 04:00:00 |
| 5 | 7 | 2014-10-16 05:00:00 |
+----+-----------+---------------------+
--
ちなみに先述したような結果を取り出すのにTEMPORARY TABLEを使っても同じことができる。
ただテンポラリテーブルは色々と制限があったりする。
ネストしすぎてSQLの可読性が下がるような場合を除いて、基本的に使わない方が良いのではないかと思っている。
--
最後に、一度グルーピングしてしまうと他の色々な操作がしづらくなる。
なので先ほどのSQL文をもう一階層ネストしてやると利用しやすくなる。
> SELECT * FROM (SELECT * FROM (SELECT * FROM t_contact ORDER BY regist_date DESC) AS temp1 GROUP BY office_id ORDER BY id) AS temp2 WHERE ...;
うん、可読性下がるねえ…
あーあと某サイトで見たけどMAX関数を使ったやり方はダメ。
結果がおかしくなる。
Category "DB・SQL" の最新記事
- [MySQL] 住所の文字列検索をサブクエリを使って実装する (0)
- [MySQL]1932 Table doesn't exist in engine エラーの解決方法 (0)
- [MySQL]Error Code: 1292. Truncated incorrect DOUBLE value (2)
- [MySQL]1行コマンドでMySQLにログインする方法 (0)
- [MySQL]DELETE文でサブクエリを使う (0)
- [MySQL]インポートで「ERROR 2006 (HY000) at line 2: MySQL server has gone away」のエラー (0)
- [MySQL]文字数のカウントはchar_length()、バイト数の長さはlength() (0)
- ダメなDBカラム名の付け方 (0)
- [MySQL] 厳格なSQLモードにする sql_mode='STRICT_ALL_TABLES' (0)
- MySQL Workbench 1000件のLimitを解除する方法 (0)