MySQL5.1で親ID毎に並び順を指定したツリー表示について

質問

id：自ID
p_id：親ID
sort：同じ親ID毎のソート順
の以下データを持つテーブルaがあるとします。

id|p_id|sort
1|0 |1
2|1 |2
3|1 |3
4|0 |2
5|4 |3
6|4 |2
7|5 |1

上記テーブルaから、SELECT文だけで
以下のような表示を行う事は可能でしょうか？

1
+-+-2
| +-3
4
+-+-6
+-5
+-7

並び順だけでも上記のように上から123465の順で取得できることでもOKです。

回答No.2

yambejp · ベストアンサー

調べてみました。

現在のデータの持ち方は「隣接リストモデル」といって、
SQL的には効率的なデータですが木構造を表現するには冗長すぎるようです。
このデータを入れ子構造モデルという形式に変換することで、
簡単に木構造を表示・集計することが可能です。

例）テーブルstack
node lft rgt
1 1 6
2 2 3
3 4 5
4 7 12
5 10 13
6 8 9
7 11 12

CREATE TABLE stack(node char(10),lft int,rgt int);
INSERT INTO stack(node,lft,rgt) VALUES
('1', 1, 6),
('2', 2, 3),
('3', 4, 5),
('4', 7, 12),
('5', 10, 13),
('6', 8, 9),
('7', 11, 12);

これを以下のSQLで可視化します。
SELECT CONCAT( REPEAT( "\t", COUNT( parent.node ) -1 ) , me.node ) AS list
FROM stack AS me, stack AS parent
WHERE me.lft
BETWEEN parent.lft
AND parent.rgt
GROUP BY me.node
ORDER BY me.lft;

肝心の隣接リストモデルから入れ子構造モデルへの変換プロセスですが
プロシージャを使えばなんとか実現可能のようです。
以下に参考サイトをあげておきます。
（mysql用ではないのでちょっと難解かもしれませんけどね・・・）

http://www.geocities.jp/mickindex/database/db_tree_ns.html#LocalLink-adj_to_set

回答No.1

yambejp

なんか簡単にできそうな気がするのですが、ぱっとおもいつかないので
ベタに処理をしてみました。

SELECT id as id_1,p_id as pid_1,sort as sort_1,NULL as id_2,NULL as pid_2,NULL as sort_2,NULL as id_3,NULL as pid_3,NULL as sort_3 FROM hoge WHERE p_id=0
UNION(
SELECT H1.id,H1.p_id,H1.sort,H2.id,H2.p_id,H2.sort,NULL,NULL,NULL FROM hoge AS H1
INNER JOIN hoge AS H2
ON H1.id=H2.p_id
WHERE H1.p_id=0
)
UNION(
SELECT H1.id,H1.p_id,H1.sort,H2.id,H2.p_id,H2.sort,H3.id,H3.p_id,H3.sort FROM hoge AS H1
INNER JOIN hoge AS H2 ON H1.id=H2.p_id
INNER JOIN hoge AS H3 ON H2.id=H3.p_id
WHERE H1.p_id=0
)
ORDER BY sort_1,sort_2,sort_3

ここから、idだけを抜き出すにはこんなかんじ・・・

SELECT COALESCE(id_3,id_2,id_1) as id FROM (
SELECT id as id_1,p_id as pid_1,sort as sort_1,NULL as id_2,NULL as pid_2,NULL as sort_2,NULL as id_3,NULL as pid_3,NULL as sort_3 FROM hoge WHERE p_id=0
UNION(
SELECT H1.id,H1.p_id,H1.sort,H2.id,H2.p_id,H2.sort,NULL,NULL,NULL FROM hoge AS H1
INNER JOIN hoge AS H2
ON H1.id=H2.p_id
WHERE H1.p_id=0
)
UNION(
SELECT H1.id,H1.p_id,H1.sort,H2.id,H2.p_id,H2.sort,H3.id,H3.p_id,H3.sort FROM hoge AS H1
INNER JOIN hoge AS H2 ON H1.id=H2.p_id
INNER JOIN hoge AS H3 ON H2.id=H3.p_id
WHERE H1.p_id=0
)
ORDER BY sort_1,sort_2,sort_3) AS DAMMY

お礼

回答ありがとうございます。やはりSQLでは
シンプルでなく大変そうですね。

結局、基本ひらがな順で、
ソート順がつけられた場合はその順番という複雑な仕様になり・・
SQLでの取得は諦めプログラムで
親から辿る方式にする事になりました。