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

Question

ちゃぼりん

30

28もっと見る

150pt

コンピュータネタ・ジョーク

あるレコードに関連した情報を持つレコードを全て抜き出せる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;

回答の条件

1人2回まで

登録：2007/06/19 11:30:21
終了：2007/06/21 10:02:28

※ 有料アンケート・ポイント付き質問機能は2023年2月28日に終了しました。

memo77 2007/07/13 23:27:32

たまたまこの処理を使うプログラムを組む機会があったので使ってみたら、回答したSQLはミスだらけ(笑)
ユーザー定義関数としてテーブルを帰す形でつくったので、訂正の意味も含めて置いておきます。

>>
ALTER FUNCTION dbo.MemberOf
(
@LogonID nvarchar(64)
)
RETURNS @MemberOf TABLE (GroupID nvarchar(64))
AS

-- PRIMARY KEY(GroupID)
BEGIN

--まずユーザーが直接所属するグループを一時テーブルに追加
INSERT INTO @MemberOf (GroupID)
SELECT GroupID
FROM GroupMember
WHERE MemberID = @LogonID

WHILE @@ROWCOUNT>0
BEGIN
INSERT INTO @MemberOf
SELECT T1.GroupID
FROM GroupMember AS T1
--次に一時テーブルに追加されたグループを配下にしているグループのうち
INNER JOIN @MemberOf AS T2
ON T1.MemberID=T2.GroupID
--まだ一時テーブルに追加されていないもの
LEFT OUTER JOIN @MemberOf AS T3
ON T1.GroupID=T3.GroupID
WHERE T3.GroupID IS NULL
--をグルーピングして追加
GROUP BY T1.GroupID
END

RETURN
END
<<

人力検索はてな SQLの書き方、考え方についてアドバイスを.. 2008-02-01 00:08:46

「あの人に答えてほしい」「この質問はあの人が答えられそう」というときに、回答リクエストを送ってみてましょう。

これ以上回答リクエストを送信することはできません。制限について

リクエスト送信済

回答リクエストを送信したユーザーはいません

memo77 · Answer 1 · 2007-06-19T12:28:30+09:00

データベース不明ですが、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

chuken_kenkou · Answer 2 · 2007-06-20T19:05:32+09:00

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

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

chuken_kenkou · Answer 3 · 2007-06-20T18:37:49+09:00

再帰的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
;

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

回答（3件）

memo77238202007/06/19 12:28:30

chuken_kenkou722542007/06/20 19:05:32

chuken_kenkou722542007/06/20 18:37:49

コメント（1件)

この質問への反応（ブックマークコメント）

トラックバック