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

回答(3件)

id:memo77 No.1

memo77回答回数238ベストアンサー獲得回数202007/06/19 12:28:30

ポイント50pt

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

id:Chaborin

ありがとうございます。

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

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

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

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

2007/06/19 12:43:31
id:chuken_kenkou No.2

chuken_kenkou回答回数722ベストアンサー獲得回数542007/06/20 19:05:32

ポイント100pt

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

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

id:chuken_kenkou No.3

chuken_kenkou回答回数722ベストアンサー獲得回数542007/06/20 18:37:49

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

id:Chaborin

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


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

・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は網羅できているみたいですね。

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

2007/06/21 10:01:04
  • id:memo77
    たまたまこの処理を使うプログラムを組む機会があったので使ってみたら、回答した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
    <<

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

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

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

絞り込み :
はてなココの「ともだち」を表示します。
回答リクエストを送信したユーザーはいません