【最大10000ポイント】当たる!!質問投稿キャンペーン!

次のようなテーブル構造で

CREATE TABLE IF NOT EXISTS area (
id int(11) NOT NULL AUTO_INCREMENT,
parent_id INT,
name varchar(64),
level int NOT NULL,
PRIMARY KEY(id),
FOREIGN KEY (parent_id) REFERENCES area(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO area (parent_id, name, level) VALUES (NULL, '地球', 1);
INSERT INTO area (parent_id, name, level) VALUES (1, '日本', 2);
INSERT INTO area (parent_id, name, level) VALUES (2, '東京都', 3);
INSERT INTO area (parent_id, name, level) VALUES (3, '千代田区', 4);
INSERT INTO area (parent_id, name, level) VALUES (2, '神奈川県', 3);
INSERT INTO area (parent_id, name, level) VALUES (4, '大手町', 5);
INSERT INTO area (parent_id, name, level) VALUES (7, '1', 6);

これを普通に取得すると上から順番(挿入順)に表示されますが、
次のように

地球
 日本
  東京都
   千代田区
    大手町
     1
  神奈川県

という順番で取得したいのです。
どのようなSQL文を書けば良いのでしょうか?

A 回答 (6件)

#4、一部バグがあるのでプロシージャ修正



DROP PROCEDURE IF EXISTS SET_LR;
DELIMITER //
CREATE PROCEDURE SET_LR()
BEGIN
DECLARE a INT DEFAULT 0;
DECLARE done INT DEFAULT 0;
DECLARE CUR CURSOR FOR
SELECT id FROM area WHERE level=0 ORDER BY parent_id ASC,id DESC;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
UPDATE area SET level=0,l=0,r=0;
UPDATE area SET level=1,l=(SELECT @a:=@a+1 FROM (SELECT @a:=0) AS sub),r=@a:=@a+1
WHERE parent_id IS NULL
ORDER BY id;

OPEN CUR;
REPEAT
FETCH CUR INTO a;
IF NOT done THEN
SET @id=a;
SET @sql='UPDATE area as a1,area as a2,area 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=?';
PREPARE stmt from @sql;
EXECUTE stmt USING @id;
SET @sql='UPDATE area as a1,area as a2,area 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=?';
PREPARE stmt from @sql;
EXECUTE stmt USING @id;
END IF;
UNTIL done END REPEAT;
CLOSE CUR;
END
//
DELIMITER ;
    • good
    • 0
この回答へのお礼

ご回答ありがとうございます。
たしかに両端に数値がセットされました。
これで例えばこのページ
http://www.geocities.jp/mickindex/database/db_tr …
にあるように様々なクエリで目的の結果が得られますね。
参考になりました。

PHPの再帰処理によっても似たようなことができたのでそこで疑問が浮かんだのですが、
行の数が増えていった場合、PHPの再帰処理だとこのSQL一文によって得られた結果よりも
かなり速度の差がでてくるのでしょうか?

お礼日時:2016/05/25 17:54

>PHPの再帰処理によっても似たようなことができた



構造を表示するだけならどちらでもたいして変わらないと思います
集計したり階層の構成を変えたりを考えるとプロシージャで
処理した方がストレスないのかなぁ・・
    • good
    • 0
この回答へのお礼

ご回答ありがとうございます。
なるほど、そこらへんは実際に使用して試行錯誤してみたほうが良さそうですね。
参考になりました。

お礼日時:2016/05/28 17:06

参考までに入れ子モデルの構造でプロシージャをつかったテストです



//テーブル作成
CREATE TABLE area (id NOT NULL ,parent_id INT,name varchar(64),level int NOT NULL,l int not null,r int not null);
INSERT INTO area (id,parent_id, name) VALUES (1,NULL, '地球'),(2,1, '日本'),(3,2, '東京都'),(4,3, '千代田区'),(5,2, '神奈川県'),(6,4, '大手町'),(7,6, '1'),(8,NULL, 'test'),(10,9, 'test');

※注意
レベルは自動、id=8は先頭の分岐、id=10は親id=9がないためどこにもつながらないデータ
lはデータの左側、rはデータの右側だと思ってください

//プロシージャの作成
DROP PROCEDURE IF EXISTS SET_LR;
DELIMITER //
CREATE PROCEDURE SET_LR()
BEGIN
DECLARE a INT DEFAULT 0;
DECLARE done INT DEFAULT 0;
DECLARE CUR CURSOR FOR
SELECT id FROM area WHERE level=0 ORDER BY parent_id ASC,id DESC;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
UPDATE area SET level=0,l=0,r=0;
UPDATE area SET level=1,l=(SELECT @a:=@a+1 FROM (SELECT @a:=0) AS sub),r=@a:=@a+1
WHERE parent_id IS NULL;
OPEN CUR;
REPEAT
FETCH CUR INTO a;
IF NOT done THEN
SET @id=a;
SET @sql='UPDATE area as a1,area as a2,area as a3,area as a4 SET a1.l=a2.l+1,a1.r=a2.l+2,a1.level=a2.level+1,a3.l=a3.l+2, a4.r=a4.r+2 WHERE a1.parent_id=a2.id AND a2.l<a3.l AND a2.l<a4.r AND a1.id=?';
PREPARE stmt from @sql;
EXECUTE stmt USING @id;
END IF;
UNTIL done END REPEAT;
CLOSE CUR;
END
//
DELIMITER ;

//実行
CALL SET_LR;

課題:分岐は最後まで表示して、その後ろに次の分岐を表示する
SELECT * FROM area WHERE LEVEL>0 ORDER BY l ASC;
    • good
    • 0

なるほどねidでリレーションしていましたね、


ただしデータの投入のやり方が変です
auto_incrementで付けたidをparent_idに振るのは超危険ですね
実際例示データも最後のデータがずれてます(親idは6のはず?)
明示的にidも手動で振ってください
INSERT INTO area (id,parent_id, name, level) VALUES (1,NULL, '地球', 1),(2,1, '日本', 2),(3,2, '東京都', 3),(4,3, '千代田区', 4),(5,2, '神奈川県', 3),(6,4, '大手町', 5),(7,6, '1', 6);

さて具体的な方法ですが、意外にめんどうなケースです。
この手の階層処理は大きく3つのやり方があります
・隣接リストモデル
・入れ子集合モデル
・経路列挙モデル

ご提示の方法は隣接リストモデルですが、親をループしてさかのぼる処理が必要で
プロシージャを使わない場合は階層数分のjoinが必要になり煩雑です
入れ子集合モデルにコンバートして参照するなど工夫が必要かもしれません

一度「階層構造 mysql」で情報を収集してみてください。
そのうえで分からないことがあれば質問してもらえばフォローできるかもしれません
    • good
    • 0
この回答へのお礼

ご回答ありがとうございます。

>実際例示データも最後のデータがずれてます(親idは6のはず?)
たしかにそうでしたね。。
アドバイスありがとうございます。

一応、PHP(ZendFramework-2のデータベースモジュール)で再帰関数を使ってできたのですが、
SQLの記述だけで以下のようなことを実現する方法がどうしてもわかりません・・・
似たような構造になりそうではありますが、、

public function sortedFetchAll($parent_id, &$rows)
{
$sql = '';
if (is_null($parent_id)) {
$sql = 'SELECT * FROM area WHERE parent_id is null';
} else {
$sql = 'SELECT * FROM area WHERE parent_id = ' . $parent_id;
}
$adapter = $this->getAdapter();
$statement = $adapter->createStatement($sql);
$res = $statement->execute();
foreach ($res as $row) {
$rows[] = $row;
$this->sortedFetchAll($row['id'], $rows);
}
}

お礼日時:2016/05/23 19:16

千代田区の親id=3は東京と神奈川どちらかどうやって見分けるの?


よくよく仕様を検討してください
    • good
    • 0
この回答へのお礼

ご回答ありがとうございます。

おそらくlevelの存在がわかりづらくなっている原因?だと思うのですが、
levelはあくまで階層の位置(深さ)を表しているにすぎません。
その親id=3というのは、外部キー
FOREIGN KEY (parent_id) REFERENCES area(id)
でも指定してあるように、areaテーブル(自身のテーブル)のidを指しているので
この場合の千代田区の親は、idが3(parent_idが3である行(この場合だと千代田区の行)を参照するのではないので注意)の行の東京になります。
なのでちゃんと階層構造はできているのです。
PHPの表示でも試しましたが、親から子へリンクをたどれるようにはなっています。
ただどうやって質問のような並び順で一度に表示できるのかがわからないのです・・・

お礼日時:2016/05/22 18:29

提示のデータでは東京と神奈川が全く同じ


内容なので、ご希望のような結果は導けません
    • good
    • 0
この回答へのお礼

ご回答ありがとうございます。

>提示のデータでは東京と神奈川が全く同じ内容
たしかにparent_idとlevelは同じではありますが、
そのparent_idを参照している子があるかないかも違いますし
そもそもname自体が違うので全く同じ内容ではないと思うのですが、、、
このテーブル構造ではその識別はできないのでしょうか?

お礼日時:2016/05/21 19:29

お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!

関連するカテゴリからQ&Aを探す


人気Q&Aランキング