風柳メモ

ソフトウェア・プログラミング関連の覚書が中心。

連結リストになっているテーブルの関連するレコードのみをSELECTするためのSQL文を知りたい

たとえば、MySQL上で次のようなテーブルを定義して、

CREATE TABLE `t_history` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `memo` text NOT NULL,
  `next_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;

次のようにレコードがセットされているときに、

mysql> SELECT * FROM t_history ORDER BY id;
+----+-----------------+---------+
| id | memo            | next_id |
+----+-----------------+---------+
|  1 | A               |       4 |
|  2 | B               |       7 |
|  3 | C               |       6 |
|  4 | A 変更その1     |       5 |
|  5 | A 変更その2     |       8 |
|  6 | C 変更その1     |    NULL |
|  7 | B 変更その1     |       9 |
|  8 | A 変更その3     |    NULL |
|  9 | B 変更その2     |    NULL |
+----+-----------------+---------+

id=8(A の変更の最新: next_id = NULL)を指定したら、

+----+-----------------+---------+
| id | memo            | next_id |
+----+-----------------+---------+
|  1 | A               |       4 |
|  4 | A 変更その1     |       5 |
|  5 | A 変更その2     |       8 |
|  8 | A 変更その3     |    NULL |
+----+-----------------+---------+

のような結果(A の履歴のみを抽出)を取得したいのだけれど、これを一発で取り出せるような SQL 文はないだろうか…?

追記(2014/10/13)

コメントで教えて頂きました。

PostgreSQLの with recursive をMySQLでエミュレートする | Hack



うーん…やっぱり連結リストにするより、たとえば

CREATE TABLE `t_history` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `memo` text NOT NULL,
  `root_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;

みたいな構造にして、

mysql> SELECT * FROM t_history ORDER BY id;
+----+-----------------+---------+
| id | memo            | root_id |
+----+-----------------+---------+
|  1 | A               |    NULL |
|  2 | B               |    NULL |
|  3 | C               |    NULL |
|  4 | A 変更その1     |       1 |
|  5 | A 変更その2     |       1 |
|  6 | C 変更その1     |       3 |
|  7 | B 変更その1     |       2 |
|  8 | A 変更その3     |       1 |
|  9 | B 変更その2     |       2 |
+----+-----------------+---------+

な感じにレコード挿入していって、

mysql> SELECT * FROM t_history WHERE id = 1 OR root_id = 1 ORDER BY id;
+----+-----------------+---------+
| id | memo            | root_id |
+----+-----------------+---------+
|  1 | A               |    NULL |
|  4 | A 変更その1     |       1 |
|  5 | A 変更その2     |       1 |
|  8 | A 変更その3     |       1 |
+----+-----------------+---------+

で履歴を取得、みたいな方が素直かなぁ…。
これなら途中のレコードを消しても、連結リストみたいに繋げなおす必要もないし。