最新のレコードに対する検索

MYSQL DB

今回は、検索に関して書きたいと思います。

DB構成

mysql> DESCRIBE homeworks;
+------------+----------+------+-----+---------+----------------+
| Field      | Type     | Null | Key | Default | Extra          |
+------------+----------+------+-----+---------+----------------+
| id         | int(11)  |      | PRI | NULL    | auto_increment |
| grade      | int(11)  | YES  |     | NULL    |                |
| created_at | datetime | YES  |     | NULL    |                |
+------------+----------+------+-----+---------+----------------+


データ

mysql> SELECT * FROM homeworks;

+----+-------+---------------------+
| id | grade | created_at          |
+----+-------+---------------------+
|  1 |     5 | 2010-04-01 12:00:00 |
|  2 |     1 | 2010-05-01 12:00:00 |
|  3 |     5 | 2010-06-01 12:00:00 |
|  4 |     4 | 2010-07-01 12:00:00 |
|  5 |     3 | 2010-08-01 12:00:00 |
|  6 |     2 | 2010-09-01 12:00:00 |
+----+-------+---------------------+


検索条件

homeworks.grade = 5


二つの最新の概念

検索条件に当てはまるレコードの中で、最新のレコード

mysql> SELECT * 
    -> FROM homeworks
    -> WHERE homeworks.created_at = (SELECT MAX(homeworks.created_at) FROM homeworks WHERE homeworks.grade = 5) AND homeworks.grade = 5;

+----+-------+---------------------+
| id | grade | created_at          |
+----+-------+---------------------+
|  3 |     5 | 2010-06-01 12:00:00 |
+----+-------+---------------------+
1 rows in set (0.00 sec)
  • 今回の検索の条件に関して
    • WHERE句の中にサブクエリ(副問い合わせ)を作成しております
    • サブクエリの内容
      1. grade = 5のレコードを抽出
      2. created_atがMAXなレコードのcreated_atを返す
    • サブクエリから返ってきたcreated_atと同じcreated_atを持っているかつgradeが5なレコードを返す

最新のレコードで検索条件に当てはまるレコード

mysql> SELECT *
    -> FROM (SELECT MAX(homeworks.created_at) AS created_at FROM homeworks) recent_homeworks, homeworks
    -> WHERE homeworks.created_at = recent_homeworks.created_at AND homeworks.student_id = recent_homeworks.student_id AND homeworks.grade = 5;

Empty set (0.00 sec)
  • 今回の検索の条件に関して
    • FROM句の中にサブクエリを作成しております。
    • サブクエリの内容
      1. homeworksの中で最大のcreated_atを持っているレコード群を生成
      2. SELECT MAX(homeworks.created_at)で生成されたカラム名をcreated_atと名付ける
      3. また、生成されたレコード群をrecent_homeworksと名付ける(擬似的な最新のhomeworksのテーブルのイメージ)
    • 最新のレコードはgrade!=5なのでEmptyが返ってくる


では、最新のレコードがgrade=5であればどうなるか

mysql> INSERT INTO homeworks (grade, created_at) VALUES (5, '2010-10-01 12:00:00');

Query OK, 1 row affected (0.02 sec)

mysql> SELECT * FROM homeworks;

+----+-------+---------------------+
| id | grade | created_at          |
+----+-------+---------------------+
|  1 |     5 | 2010-04-01 12:00:00 |
|  2 |     1 | 2010-05-01 12:00:00 |
|  3 |     5 | 2010-06-01 12:00:00 |
|  4 |     4 | 2010-07-01 12:00:00 |
|  5 |     3 | 2010-08-01 12:00:00 |
|  6 |     2 | 2010-09-01 12:00:00 |
|  7 |     5 | 2010-10-01 12:00:00 |
+----+-------+---------------------+
7 rows in set (0.01 sec)

mysql> SELECT *
    -> FROM (SELECT MAX(homeworks.created_at) AS created_at, homeworks.student_id FROM homeworks) recent_homeworks, homeworks
    -> WHERE homeworks.created_at = recent_homeworks.created_at AND homeworks.grade = 5;

+----+-------+---------------------+
| id | grade | created_at          |
+----+-------+---------------------+
|  7 |     5 | 2010-10-01 12:00:00 |
+----+-------+---------------------+
1 rows in set (0.01 sec)
  • うまくSELECTできました！


間違いなどございましたら、ご指摘くださいませ。


最後までお読みいただきありがとうございました。