人力検索はてな
モバイル版を表示しています。PC版はこちら
i-mobile

あるレコードに関連した情報を持つレコードを全て抜き出せるSQLを書きたいです。

下のSQLでは、NAMEで自レコードの名前を持ち、RELATEFROMで関連するレコードNAMEを示しています。
このとき、jkondoというNAMEの親レコードがあり、jkondoに関連しているレコードがnaoyaとreikonになりますが、cinnamonという名前のレコードも、reikonの関連元であるjkondoに関連し、さらにmojaもcinnamonに関連するので、同様にさかのぼってjkondoの関連として扱いたいです。

つまり、RELATEFROMがjkondoであるレコードを再帰的に呼び出したいのですが、そのようなSELECT文を書くのは可能でしょうか。
参考ページでも良いですし、SQLでも結構です。

下記にDDLとDMLを記します。

CREATE TABLE TESTER (
NAME VARCHAR(10),
RELATEFROM VARCHAR(10)
);

INSERT INTO TESTER VALUES ('jkondo','');
INSERT INTO TESTER VALUES ('naoya','jkondo');
INSERT INTO TESTER VALUES ('reikon','jkondo');
INSERT INTO TESTER VALUES ('cinnamon','reikon');
INSERT INTO TESTER VALUES ('moja','cinnamon');
COMMIT;

●質問者: ちゃぼりん
●カテゴリ:コンピュータ ネタ・ジョーク
✍キーワード:DDL DML jkondo name SELECT
○ 状態 :終了
└ 回答数 : 3/3件

▽最新の回答へ

1 ● memo77
●50ポイント

データベース不明ですが、SQLServerだとして、こんな感じでどうでしょう。未検証なので動くかどうか不明ですが。

CREATE #T (NAME VARCHAR(10) PRIMARY KEY(NAME)

INSERT INTO #T (NAME) SELECT 'jkondo'

WHILE @@ROWCOUNT>0

BEGIN

INSERT INTO #T

SELECT T1.NAME

FROM TESTER AS T1.

INNER JOIN #T AS T2

ON T1.NAME=T2.RELATEFROM

LEFT OUTER JOIN #T AS T3

ON T1.NAME=T3.NAME

WHERE T3.NAME IS NULL

GROUP BY T1.NAME

END

◎質問者からの返答

ありがとうございます。

これはSQL-ServerのT-SQLですね。

で、なくなるまでWHILEで回して#T一時テーブルに書き出すと。なるほど。

DBMSは不問です。いつもお使いの慣れたものでお願いします。

できるならストアドでなく、SELECT文1発を希望していますが、ストアドでも歓迎です。


2 ● chuken_kenkou
●100ポイント

OracleやPostgreSQLには、階層問い合わせという機能があり、今回のようなデータになら適用可能だと思います。

[ThinkIT] 第5回:SQL文の移行(2) (1/2)


3 ● chuken_kenkou
●0ポイント

再帰的SQLについては、SQL99で規定されています。

新しい業界標準「SQL99」詳細解説


これを実装しているRDBMSはまだ多くないのですが、SQL Server 2005やHiRDBでは実装済です。

SQL Server 2005でなら、以下のようなSQLになります。

with cte(name,relatefrom,n) as
(
 select name,relatefrom,1
 from tester
 where relatefrom=''
 union all
 select y.name,y.relatefrom,n+1
 from cte as x,tester as y
 where x.name=y.relatefrom
)
 select name,relatefrom,n
 from cte
;

◎質問者からの返答

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


えーと、ここまでまとめると

・SQL99では定義済(WITH文)

実装しているRDBMSは、SQL-Server2005とhirdb。

・Oracleでは独自実装(connectby関数)

・PostgreSQLでは独自実装(START WITH?CONNECT BY句)

・もちろん各種ストアドプロシージャでも可


早速「再帰問い合わせ」というキーワードを使って調べたところ、

FireBird2.1でもSQL99形式で実装済

http://blog.kimuradb.com/?eid=533912


あ・・・DB2 9でも実装されていました。

http://www-06.ibm.com/jp/software/data/developer/column/iroha/38...


主要なRDBMSは網羅できているみたいですね。

ありがとうございました。

関連質問


●質問をもっと探す●



0.人力検索はてなトップ
8.このページを友達に紹介
9.このページの先頭へ
対応機種一覧
お問い合わせ
ヘルプ/お知らせ
ログイン
無料ユーザー登録
はてなトップ