トップ回答者
再帰クエリで解けませんか?

質問
-
お世話になります。SQL Server 2005で、以下のコードで再帰クエリのアンカーまで書いたのですが、再帰の部分でどうにも先が続かなくなってしまいました。よろしくお願いします。
IF OBJECT_ID('dbo.TEST') IS NOT NULL DROP TABLE dbo.TEST; GO CREATE TABLE dbo.TEST (ID int NOT NULL PRIMARY KEY, Code nchar(1) NOT NULL, Data nvarchar(max) NOT NULL); INSERT INTO dbo.TEST VALUES(1, 'a', 'AA'); INSERT INTO dbo.TEST VALUES(2, 'a', 'BBBB'); INSERT INTO dbo.TEST VALUES(3, 'b', 'CCC'); INSERT INTO dbo.TEST VALUES(4, 'a', 'D'); INSERT INTO dbo.TEST VALUES(5, 'a', 'EEEE'); INSERT INTO dbo.TEST VALUES(6, 'b', 'FF'); INSERT INTO dbo.TEST VALUES(7, 'b', 'GG'); INSERT INTO dbo.TEST VALUES(8, 'a', 'H'); WITH CTE(KEYCOL, CODECOL, DATACOL) AS ( SELECT T1.ID, T1.Code, T1.Data FROM (SELECT MIN(ID) AS MINID, Code AS MINCODE FROM dbo.TEST GROUP BY Code) AS T0 INNER JOIN dbo.TEST AS T1 ON T0.MINID = T1.ID ) SELECT * FROM CTE;
欲しい結果は以下のとおりです。KEYCOL CODECOL DATACOL----------- ------- -------------------1 a AABBBBDEEEEH3 b CCCFFGG本来一行で表現されるべきレコードがいくつかに分割されているのをマージしたいという意味です。
回答
-
とりあえず、こんな感じかなぁ?
WITH CTE(KEYCOL, CODECOL, DATACOL, DATALEVEL) AS ( SELECT T1.ID, T1.Code, T1.Data, 1 FROM (SELECT MIN(ID) AS MINID, Code AS MINCODE FROM dbo.TEST GROUP BY Code) AS T0 INNER JOIN dbo.TEST AS T1 ON T0.MINID = T1.ID UNION ALL SELECT T2.id, T2.Code, CTE.DATACOL + T2.Data, CTE.DATALEVEL + 1 FROM dbo.TEST T2 INNER JOIN CTE ON T2.Code = CTE.CODECOL AND T2.id > CTE.KEYCOL ) --select * from CTE select (select min(T1.KEYCOL) from CTE T1 where T1.CODECOL = T0.CODECOL), T0.CODECOL, (select top 1 T2.DATACOL from CTE T2 where T2.CODECOL = T0.CODECOL order by T2.DATALEVEL desc) from CTE T0 group by T0.CODECOL
★良い回答には回答済みマークを付けよう! わんくま同盟 MVP - Visual C# http://blogs.wankuma.com/trapemiya/- 回答としてマーク じふ 2010年3月19日 3:52
-
with work(ID,Code,Data) as( select 1, 'a', 'AA' union select 2, 'a', 'BBBB' union select 3, 'b', 'CCC' union select 4, 'a', 'D' union select 5, 'a', 'EEEE' union select 6, 'b', 'FF' union select 7, 'b', 'GG' union select 8, 'a', 'H' union select 9, 'c', 'II' union select 10, 'd', 'JJ') select min(ID) as KEYCOL,Code, (select '' + Data from work b where b.Code=a.Code order by ID FOR XML PATH('')) as DATACOL from work a group by Code; KEYCOL Code DATACOL ------ ---- ------------ 1 a AABBBBDEEEEH 3 b CCCFFGG 9 c II 10 d JJ
SQLServerの分析関数の使用例の「21. ListAgg関数とwmsys.wm_concat」
http://www.geocities.jp/oraclesqlpuzzle/sqlserver2008-sql1-olap.html#2-21
をふまえて作ってみました :-)Create A Comma Delimited List From a Column
http://code.msdn.microsoft.com/SQLExamples/Wiki/View.aspx?title=CreateACommaDelimitedList
によると、FOR XML PATHを使うサブクエリでorder byを指定すれば文字列の連結順序を指定できるようですね。- 回答の候補に設定 Aketi Jyuuzou 2010年4月22日 12:14
- 回答としてマーク じふ 2010年4月26日 18:17
- 編集済み Aketi Jyuuzou 2010年11月3日 2:53
-
このデータ構造には、重複値がないので、Row_Numberが1大きいものが最小上界であることに注目しつつ、
さらに、Row_Numberの最大値は分析関数のcount関数で求まるということで、再度、SQLServerの分析関数の使用例の「21. ListAgg関数とwmsys.wm_concat」
http://www.geocities.jp/oraclesqlpuzzle/sqlserver2008-sql1-olap.html#2-21
をふまえて、SQLServer2008で再帰SQLを使う方法で作ってみました :-)with work(ID,Code,Data) as( select 1, 'a', 'AA' union select 2, 'a', 'BBBB' union select 3, 'b', 'CCC' union select 4, 'a', 'D' union select 5, 'a', 'EEEE' union select 6, 'b', 'FF' union select 7, 'b', 'GG' union select 8, 'a', 'H' union select 9, 'c', 'II' union select 10, 'd', 'JJ'), tmp(ID,Code,Data,rn,recCnt) as( select ID,Code,Data,Row_Number() over(partition by Code order by ID), count(*) over(partition by Code) from work), rec(KeyCol,Code,rn,DATACOL,recCnt) as( select ID,Code,rn, cast(Data as varchar(20)),recCnt from tmp where rn=1 union all select a.KeyCol,a.Code,b.rn, cast(a.DATACOL + b.Data as varchar(20)),a.recCnt from rec a,tmp b where a.Code=b.Code and a.rn+1=b.rn) select * from rec where rn=recCnt order by Code; KeyCol Code rn DATACOL recCnt ------ ---- -- ------------ ------ 1 a 5 AABBBBDEEEEH 5 3 b 3 CCCFFGG 3 9 c 1 II 1 10 d 1 JJ 1
- 回答の候補に設定 Aketi Jyuuzou 2010年4月26日 11:56
- 回答としてマーク じふ 2010年4月26日 18:17
- 編集済み Aketi Jyuuzou 2010年11月3日 2:52
すべての返信
-
とりあえず、こんな感じかなぁ?
WITH CTE(KEYCOL, CODECOL, DATACOL, DATALEVEL) AS ( SELECT T1.ID, T1.Code, T1.Data, 1 FROM (SELECT MIN(ID) AS MINID, Code AS MINCODE FROM dbo.TEST GROUP BY Code) AS T0 INNER JOIN dbo.TEST AS T1 ON T0.MINID = T1.ID UNION ALL SELECT T2.id, T2.Code, CTE.DATACOL + T2.Data, CTE.DATALEVEL + 1 FROM dbo.TEST T2 INNER JOIN CTE ON T2.Code = CTE.CODECOL AND T2.id > CTE.KEYCOL ) --select * from CTE select (select min(T1.KEYCOL) from CTE T1 where T1.CODECOL = T0.CODECOL), T0.CODECOL, (select top 1 T2.DATACOL from CTE T2 where T2.CODECOL = T0.CODECOL order by T2.DATALEVEL desc) from CTE T0 group by T0.CODECOL
★良い回答には回答済みマークを付けよう! わんくま同盟 MVP - Visual C# http://blogs.wankuma.com/trapemiya/- 回答としてマーク じふ 2010年3月19日 3:52
-
お世話になります。アンカーから始めて色々の組み合わせでつないで行った結果、最も階層の深くなったものを取り出すという発想ですね。勉強になります。実はその後下記のようなコードを書いてみたのですが、再帰部分では集約関数が使えないという制約に引っかかってしまいました。アンカーから初めて最小上界を順に繋いでいきたかったのですが...
WITH CTE(KEYCOL, CODECOL, DATACOL, DATALEVEL) AS ( SELECT T1.ID, T1.Code, T1.Data, 1 FROM (SELECT MIN(ID) AS MINID, Code AS MINCODE FROM dbo.TEST GROUP BY Code) AS T0 INNER JOIN dbo.TEST AS T1 ON T0.MINID = T1.ID UNION ALL SELECT T2.ID, T2.Code, CTE.DATACOL + T2.Data, CTE.DATALEVEL + 1 FROM CTE INNER JOIN dbo.TEST AS T2 ON CTE.CODECOL = T2.Code AND CTE.KEYCOL = (SELECT MIN(ID) FROM dbo.TEST AS T3 WHERE CTE.KEYCOL < T3.ID) ) SELECT * FROM CTE ORDER BY DATALEVEL, CODECOL; /*SELECT (SELECT MIN(T3.KEYCOL) FROM CTE AS T3 WHERE T3.CODECOL = T5.CODECOL), T5.CODECOL, (SELECT TOP 1 T4.DATACOL FROM CTE AS T4 WHERE T4.CODECOL = T5.CODECOL ORDER BY T4.DATALEVEL DESC) FROM CTE AS T5 GROUP BY T5.CODECOL;*/ メッセージ 467、レベル 16、状態 1、行 15 GROUP BY、HAVING、または集計関数は、再帰共通テーブル式 'CTE' の再帰部分では許可されません。
-
実はその後下記のようなコードを書いてみたのですが、再帰部分では集約関数が使えないという制約に引っかかってしまいました。アンカーから初めて最小上界を順に繋いでいきたかったのですが...
そうなんですよ。CTEで集約関数が使えると便利なんですけどね。ちなみに外部結合などもできません。詳しくは以下に書かれています。今後、改善されるといいですね。
WITH common_table_expression (Transact-SQL)
http://msdn.microsoft.com/ja-jp/library/ms175972.aspx
★良い回答には回答済みマークを付けよう! わんくま同盟 MVP - Visual C# http://blogs.wankuma.com/trapemiya/ -
お世話になります。やはりダメなんですね。となると、再帰部分はご提案の方法を使わせていただくとして、それを取り出す方法に専念した方が良さそうですね。
IF OBJECT_ID('dbo.TEST') IS NOT NULL DROP TABLE dbo.TEST; GO CREATE TABLE dbo.TEST (ID int NOT NULL PRIMARY KEY, Code nchar(1) NOT NULL, Data nvarchar(max) NOT NULL); INSERT INTO dbo.TEST VALUES(1, 'a', 'AA'); INSERT INTO dbo.TEST VALUES(2, 'a', 'BBBB'); INSERT INTO dbo.TEST VALUES(3, 'b', 'CCC'); INSERT INTO dbo.TEST VALUES(4, 'a', 'D'); INSERT INTO dbo.TEST VALUES(5, 'a', 'EEEE'); INSERT INTO dbo.TEST VALUES(6, 'b', 'FF'); INSERT INTO dbo.TEST VALUES(7, 'b', 'GG'); INSERT INTO dbo.TEST VALUES(8, 'a', 'H'); INSERT INTO dbo.TEST VALUES(9, 'c', 'II'); INSERT INTO dbo.TEST VALUES(10, 'd', 'JJ'); WITH CTE(KEYCOL, CODECOL, DATACOL, DATALEVEL) AS ( SELECT T1.ID, T1.Code, T1.Data, 1 FROM (SELECT MIN(ID) AS MINID, Code AS MINCODE FROM dbo.TEST GROUP BY Code) AS T0 INNER JOIN dbo.TEST AS T1 ON T0.MINID = T1.ID UNION ALL SELECT T2.ID, T2.Code, CTE.DATACOL + T2.Data, CTE.DATALEVEL + 1 FROM dbo.TEST AS T2 INNER JOIN CTE ON CTE.CODECOL = T2.Code AND CTE.KEYCOL < T2.ID ) SELECT T6.KEYCOL, T6.CODECOL, T6.DATACOL, T6.DATALEVEL FROM (SELECT T4.CODECOL AS T4CODE, MAX(DATALEVEL) AS MAXLEVEL FROM CTE AS T4 GROUP BY T4.CODECOL) AS T5 INNER JOIN CTE AS T6 ON T5.T4CODE = T6.CODECOL AND T5.MAXLEVEL = T6.DATALEVEL;
上記クエリの結果は以下のようになります。KEYCOL CODECOL DATACOL DATALEVEL----------- ------- ------------- -----------9 c II 110 d JJ 17 b CCCFFGG 38 a AABBBBDEEEEH 5dbo.TESTを以下のようにUPDATEできればその後は重複削除のみになるので、とりあえずこの方向で行ってみたいと思います。1 a AABBBBDEEEEH2 a AABBBBDEEEEH3 b CCCFFGG4 a AABBBBDEEEEH5 a AABBBBDEEEEH6 b CCCFFGG7 b CCCFFGG8 a AABBBBDEEEEH9 c II10 d JJ -
PostgreSQL8.4で解いてみました :-)
with recursive tmp(ID,Code,Data) as( values(1, 'a', 'AA'), (2, 'a', 'BBBB'), (3, 'b', 'CCC'), (4, 'a', 'D'), (5, 'a', 'EEEE'), (6, 'b', 'FF'), (7, 'b', 'GG'), (8, 'a', 'H'), (9, 'c', 'II'), (10, 'd', 'JJ')), wk (ID,Code,Data,rn,recCnt) as( select ID,Code,Data,Row_Number() over(partition by Code order by ID) as rn, count(*) over(partition by Code) as recCnt from tmp), rec(KeyCol,Code,rn,DATACOL,recCnt) as( select ID,Code,rn,Data,recCnt from wk where rn=1 union all select a.KeyCol,a.Code,b.rn,a.DATACOL || b.Data,a.recCnt from rec a,wk b where a.Code=b.Code and a.rn+1=b.rn) select * from rec where rn=recCnt order by Code; keycol | code | rn | datacol | reccnt --------+------+----+--------------+-------- 1 | a | 5 | AABBBBDEEEEH | 5 3 | b | 3 | CCCFFGG | 3 9 | c | 1 | II | 1 10 | d | 1 | JJ | 1
そもそも、SQLServer2005なら、group_concatもどきを使えばシンプルなSQLになりそうですがhttp://blog.livedoor.jp/oolv/archives/370268.htmlただし、dataの連結順序の指定ができないはず・・・ -
そもそも、SQLServer2005なら、group_concatもどきを使えばシンプルなSQLになりそうですがhttp://blog.livedoor.jp/oolv/archives/370268.htmlただし、dataの連結順序の指定ができないはず・・・
-
with work(ID,Code,Data) as( select 1, 'a', 'AA' union select 2, 'a', 'BBBB' union select 3, 'b', 'CCC' union select 4, 'a', 'D' union select 5, 'a', 'EEEE' union select 6, 'b', 'FF' union select 7, 'b', 'GG' union select 8, 'a', 'H' union select 9, 'c', 'II' union select 10, 'd', 'JJ') select min(ID) as KEYCOL,Code, (select '' + Data from work b where b.Code=a.Code order by ID FOR XML PATH('')) as DATACOL from work a group by Code; KEYCOL Code DATACOL ------ ---- ------------ 1 a AABBBBDEEEEH 3 b CCCFFGG 9 c II 10 d JJ
SQLServerの分析関数の使用例の「21. ListAgg関数とwmsys.wm_concat」
http://www.geocities.jp/oraclesqlpuzzle/sqlserver2008-sql1-olap.html#2-21
をふまえて作ってみました :-)Create A Comma Delimited List From a Column
http://code.msdn.microsoft.com/SQLExamples/Wiki/View.aspx?title=CreateACommaDelimitedList
によると、FOR XML PATHを使うサブクエリでorder byを指定すれば文字列の連結順序を指定できるようですね。- 回答の候補に設定 Aketi Jyuuzou 2010年4月22日 12:14
- 回答としてマーク じふ 2010年4月26日 18:17
- 編集済み Aketi Jyuuzou 2010年11月3日 2:53
-
このデータ構造には、重複値がないので、Row_Numberが1大きいものが最小上界であることに注目しつつ、
さらに、Row_Numberの最大値は分析関数のcount関数で求まるということで、再度、SQLServerの分析関数の使用例の「21. ListAgg関数とwmsys.wm_concat」
http://www.geocities.jp/oraclesqlpuzzle/sqlserver2008-sql1-olap.html#2-21
をふまえて、SQLServer2008で再帰SQLを使う方法で作ってみました :-)with work(ID,Code,Data) as( select 1, 'a', 'AA' union select 2, 'a', 'BBBB' union select 3, 'b', 'CCC' union select 4, 'a', 'D' union select 5, 'a', 'EEEE' union select 6, 'b', 'FF' union select 7, 'b', 'GG' union select 8, 'a', 'H' union select 9, 'c', 'II' union select 10, 'd', 'JJ'), tmp(ID,Code,Data,rn,recCnt) as( select ID,Code,Data,Row_Number() over(partition by Code order by ID), count(*) over(partition by Code) from work), rec(KeyCol,Code,rn,DATACOL,recCnt) as( select ID,Code,rn, cast(Data as varchar(20)),recCnt from tmp where rn=1 union all select a.KeyCol,a.Code,b.rn, cast(a.DATACOL + b.Data as varchar(20)),a.recCnt from rec a,tmp b where a.Code=b.Code and a.rn+1=b.rn) select * from rec where rn=recCnt order by Code; KeyCol Code rn DATACOL recCnt ------ ---- -- ------------ ------ 1 a 5 AABBBBDEEEEH 5 3 b 3 CCCFFGG 3 9 c 1 II 1 10 d 1 JJ 1
- 回答の候補に設定 Aketi Jyuuzou 2010年4月26日 11:56
- 回答としてマーク じふ 2010年4月26日 18:17
- 編集済み Aketi Jyuuzou 2010年11月3日 2:52
-
お世話になります.分析関数は使用経験がなくて半分も理解できてないんですが,勉強になります.とりあえず下記のコードで求める結果は出ます.
http://social.msdn.microsoft.com:80/Forums/ja-JP/transactsql/thread/09be2b0e-033d-4ca6-99a3-b0b91d72b651
WITH CTE(KEYCOL, CODECOL, DATACOL, DATALEVEL) AS ( SELECT T1.ID, T1.Code, T1.Data, 1 FROM (SELECT MIN(ID) AS MINID, Code AS MINCODE FROM dbo.TEST GROUP BY Code) AS T0 INNER JOIN dbo.TEST AS T1 ON T0.MINID = T1.ID UNION ALL SELECT T2.ID, T2.Code, CTE.DATACOL + T2.Data, CTE.DATALEVEL + 1 FROM dbo.TEST AS T2 INNER JOIN CTE ON CTE.CODECOL = T2.Code AND CTE.KEYCOL < T2.ID ),CTE2 AS ( SELECT T6.KEYCOL, T6.CODECOL, T6.DATACOL, T6.DATALEVEL FROM (SELECT T4.CODECOL AS T4CODE, MAX(DATALEVEL) AS MAXLEVEL FROM CTE AS T4 GROUP BY T4.CODECOL) AS T5 INNER JOIN CTE AS T6 ON T5.T4CODE = T6.CODECOL AND T5.MAXLEVEL = T6.DATALEVEL ) UPDATE T SET Data = C2.DataCol FROM dbo.Test AS T INNER JOIN CTE2 AS c2 ON c2.CodeCol = T.Code