SQLで連結リストの探索のようなこと

###前提・実現したいこと

DB上に以下のようなテーブルがあるとします。

ID	WEIGHT	NEXT_ID
1	10	2
2	20	3
3	30	4
4	40	NULL
5	50	6
6	60	3

IDは主キー、WEIGHTはその要素の重み、NEXT_IDはその要素の次の要素です。

この表に対して任意のIDを指定した時に、NEXT_IDをNULLになるまで順に辿り、その間に出現したWEIGHTの合計を求めたいです。
(例えばID=1とした場合10+20+30+40=100、ID=6とした場合60+30+40=130が返却される)
※NEXT_IDがNULLにならずにいつまでもループする事は無いものとします。

###課題

現状、以下のようにPHP内で1行ずつセレクト文を発行しながら辿るような実装をしていますが、10レコード辿る場合は10回もSELECT文を発行しています。

PHP
1$id = 1;
2$sum = 0;  //重みの合計
3while($id) {
4    $sql = 'SELECT WEIGHT, NEXT_ID FROM MY_TABLE WHERE ID = ' . $id;
5
6    //ここら辺でSQLを発行して$rowにレコードを格納
7
8    $sum += $row['WEGIHT'];
9    $id = $row['NEXT_ID'];
10}

SELECTの回数が多いのは何か気持ち感じがしてて、出来れば1回のSELECT文でNEXT_IDを最後まで辿って重みの合計を持ってくるようなクエリが理想なのですが、SQLのみでそれは可能でしょうか。

「SQLでそんなこと不可能だ」、「10回なんて細かい事気にせずクエリ投げまくれ」といった意見でも結構です。

よろしくお願いします。

行動規範の内容に同意します

回答3件

SQLが不得手なテーマではありますが、近年のRDBの多くが実装しているCTE(共通テーブル式)やテーブル設計の工夫にて、
SQLのみでも解決できるテーマとなっています。

タイトルからは分かりにくいですが、
こちらでRDBを用いた階層モデル・連結リストの実装手段について言及していますのでご参考までに。

また残念ながらMySQL、MariaDBでは未サポート(MariaDBではβ版でWITH句のサポートを始めるようなことは言ってた気がする・・・)となってますが、
tacsheavenさんが言及しているように、近年のRDBは再帰問い合わせの機能を標準で実装しています。
(WITH RECURSIVE句、RECURSIVE句は付けなくても良いかも？)

ご興味がありましたら、
調べてみると良いかもしれません。
(Oracle以外では、PostgreSQL、SQL Server、DB2もサポートしてますね)

投稿2016/12/03 15:05