1createtable tree (2id intnotnullunique,3parent_id int,4levelintnotnulldefault0,5l intnotnulldefault0,6r intnotnulldefault0);78insertinto tree(id,parent_id)values9(1600,NULL),(1700,1600),(1750,1600),(1800,1700),(1820,1700),(1850,1700),(1900,1800);
level=何階層目かを計算していれる
l=左の値(計算で求める)
r=右の値(計算で求める)
(2)procedureの作成
SQL
1dropprocedureifexists SET_LR;2delimiter//3createprocedure SET_LR()4begin5declare a intdefault0;6declare done intdefault0;7declare cur cursorFOR8select id from tree wherelevel=0orderby parent_id asc,id desc;9declarecontinuehandlerfor sqlstate '02000'set done =1;10update tree setlevel=0,l=0,r=0;11update tree setlevel=1,l=(select@a:=@a+1from(select@a:=0)AS sub),r=@a:=@a+112where parent_id isnull13orderby id;14open cur;15repeat16fetch cur into a;17ifnot done then18set@id=a;19set@sql='update tree as a1,tree as a2,tree as a3 set a1.l=a2.l+1,a1.r=a2.l+2,a1.level=a2.level+1,a2.r=a2.r+2,a3.r=a3.r+2 where a1.parent_id=a2.id and a2.l<a3.r and a1.id=?';20prepare stmt from@sql;21execute stmt using@id;22set@sql='update tree as a1,tree as a2,tree as a3 set a3.l=a3.l+2 where a1.parent_id=a2.id and a2.l<a3.l and a3.id!=a1.id and a1.id=?';23prepare stmt from@sql;24execute stmt using@id;25endif;26until done endrepeat;27close cur;28end29//30delimiter;
(3)procedureの実行
※実際にはデータを追加・更新・削除するたびに実行する
SQL
1call SET_LR();
テスト
(1)単純なデータ
SQL
1select*from tree;
id
parent_id
level
l
r
1600
NULL
1
1
14
1700
1600
2
2
11
1750
1600
2
12
13
1800
1700
3
3
6
1820
1700
3
7
8
1850
1700
3
9
10
1900
1800
4
4
5
(2)階層表示
SQL
1select CONCAT(REPEAT("__",LEVEL-1), id )AS id
2from tree
3orderby l;
id
1600
__1700
____1800
______1900
____1820
____1850
__1750
(3)経路表示
SQL
1select t2.id,group_concat(t1.id orderby t1.parent_id asc)as route
2from tree as t1
3innerjoin tree as t2
4on t2.l between t1.l and t1.r
5groupby t2.id;
バッドをするには、ログインかつ
こちらの条件を満たす必要があります。