トップ回答者
SQL 文の書き方

質問
-
はじめてここを利用させていただきます。
投稿が適切でないようであればご指摘いただければと思います。現在 SQL 勉強中です。
特定の文字列の 1 つ前のレコードを抽出したいと思っています
分かる方がいれば教えていただければと思います。環境は SQL Server 2008 R2 です。
下記のようなデータで、ID,sub-ID の組み合わせは一意です。
data1 に "aaa" がある行を検索し、その sub-ID から 1 を引いた行を抽出したいと思っています。
ただし、その行の data1 が NULL の場合は 2 を引いた行を抽出、sub-ID が 1 の場合には抽出しないという条件をつけたいです。
(ID,sub-ID) の (1,4)(2,3)(3,3)(4,5)(5,1) に aaa があるので、
(1,3)(2,2)(3,1)(4,4) の行を抽出となります。
※ (3,2) は data1 に NULL があるので (3,1) を抽出、(5,1) は sub-ID から 1 を引いたら 0 になるので除外。ID sub-ID Time data1 data2
1 1 *** ggg ***
1 2 *** fff ***
1 3 *** qqq ***
1 4 *** aaa ***
2 1 *** ddd ***
2 2 *** ccc ***
2 3 *** aaa ***
3 1 *** lll ***
3 2 *** NULL ***
3 3 *** aaa ***
4 1 *** yyy ***
4 2 *** kkk ***
4 3 *** jjjj ***
4 4 *** uuu ***
4 5 *** aaa ***
5 1 *** aaa ***
期待される結果
ID sub-ID Time data1 data2
1 3 *** qqq ***
2 2 *** ccc ***
3 1 *** lll ***
4 4 *** uuu ***
- 編集済み SQL_neko 2015年3月11日 2:13
回答
-
綺麗かどうかは別として
BEGIN TRY BEGIN TRANSACTION CREATE TABLE #TEMP ([ID] INT NOT NULL ,[sub-ID] INT NOT NULL ,[TIME] VARCHAR(10) NULL ,[data1] VARCHAR(10) NULL ,[data2] VARCHAR(10) NULL ); ALTER TABLE #TEMP ADD CONSTRAINT PK_TEMP PRIMARY KEY ([ID], [sub-ID]); INSERT INTO #TEMP VALUES(1,1,'***','ggg','***') INSERT INTO #TEMP VALUES(1,2,'***','fff','***') INSERT INTO #TEMP VALUES(1,3,'***','qqq','***') INSERT INTO #TEMP VALUES(1,4,'***','aaa','***') INSERT INTO #TEMP VALUES(2,1,'***','ddd','***') INSERT INTO #TEMP VALUES(2,2,'***','ccc','***') INSERT INTO #TEMP VALUES(2,3,'***','aaa','***') INSERT INTO #TEMP VALUES(3,1,'***','lll','***') INSERT INTO #TEMP VALUES(3,2,'***',NULL,'***') INSERT INTO #TEMP VALUES(3,3,'***','aaa','***') INSERT INTO #TEMP VALUES(4,1,'***','yyy','***') INSERT INTO #TEMP VALUES(4,2,'***','kkk','***') INSERT INTO #TEMP VALUES(4,3,'***','jjjj','***') INSERT INTO #TEMP VALUES(4,4,'***','uuu','***') INSERT INTO #TEMP VALUES(4,5,'***','aaa','***') INSERT INTO #TEMP VALUES(5,1,'***','aaa','***') SELECT MAIN.* FROM #TEMP AS MAIN WHERE EXISTS ( SELECT SUB.* FROM #TEMP AS SUB WHERE SUB.[ID] = MAIN.[ID] AND SUB.[data1] = 'aaa' AND MAIN.[sub-ID] = (SELECT MAX([sub-ID]) FROM #TEMP WHERE [ID] = SUB.[ID] AND [sub-ID] < SUB.[sub-ID] AND [data1] IS NOT NULL) ) ORDER BY MAIN.[ID], MAIN.[sub-ID] ROLLBACK TRANSACTION END TRY BEGIN CATCH PRINT ERROR_MESSAGE() ROLLBACK TRANSACTION END CATCH
-
実際には-1してNULLだから、-2してもまだNULLだったら、-3しないといけない、みたいな落ちが付くのでは?と妄想してみました。
WITH Table_Rank as ( select t2.*,row_number() over (Partition by t2.id order by t2.[sub-id] desc ) as rank FROM TABLE t1 INNER JOIN TABLE t2 on t1.id = t2.id and t1.[sub-id] > t2.[sub-id] where t1.data1 = 'AAA' and t2.data1 is not null ) select ID,[sub-id], time, data1, data2 FROM Table_Rank where rank = 1
jzkey
-
了解しました。では、以下のようなSQLを考えてみました。
with CTE as ( select * from IDTEST where data1 = 'aaa' and [sub-ID] > 1 ) select i.* from CTE c inner join IDTEST i on i.ID = c.ID and i.[sub-ID] = (select max([sub-id]) from IDTEST it where it.ID = c.ID and it.[sub-ID] < c.[sub-ID] and it.data1 is not null)
#今気づきましたが、aviator__さんと同じような考え方になりました。aviator__さんは最初から理解されていたんですね!★良い回答には回答済みマークを付けよう! MVP - .NET http://d.hatena.ne.jp/trapemiya/
-
自己結合を使わないで書いてみました。
WITH temp1 AS ( SELECT *, MIN(CASE WHEN data1 = 'aaa' THEN [sub-ID] ELSE null END) OVER (PARTITION BY ID) AS [sub-ID-aaa] FROM Table1 WHERE data1 IS NOT NULL ), temp2 AS ( SELECT *, ROW_NUMBER() OVER (PARTITION BY ID ORDER BY [sub-ID] desc) AS RowNo FROM temp1 WHERE [sub-ID] < [sub-ID-aaa] ) SELECT ID,[sub-id], Time, data1, data2 FROM temp2 WHERE RowNo = 1;
実行の手順は下記のようになります。
1)data1 が null の行を除外
2)各IDグループについて data1 = 'aaa' である最初の sub-ID を求める→これを sub-ID-aaa とする
3)sub-ID が sub-ID-aaa 未満の行を抽出(先頭行が data1 = 'aaa' となっているIDグループはここで除外)
4)各IDグループについて sub-ID の降順に並べる
5)各IDグループの先頭行を抽出
前提として同じIDで data1 = 'aaa' を満たす複数の行がある場合は sub-ID が小さいものを有効にします。
仮にテーブル名を Table1 としています。これを適宜お使いのものに変えてください。
http://systemartlaboratory.com/
すべての返信
-
綺麗かどうかは別として
BEGIN TRY BEGIN TRANSACTION CREATE TABLE #TEMP ([ID] INT NOT NULL ,[sub-ID] INT NOT NULL ,[TIME] VARCHAR(10) NULL ,[data1] VARCHAR(10) NULL ,[data2] VARCHAR(10) NULL ); ALTER TABLE #TEMP ADD CONSTRAINT PK_TEMP PRIMARY KEY ([ID], [sub-ID]); INSERT INTO #TEMP VALUES(1,1,'***','ggg','***') INSERT INTO #TEMP VALUES(1,2,'***','fff','***') INSERT INTO #TEMP VALUES(1,3,'***','qqq','***') INSERT INTO #TEMP VALUES(1,4,'***','aaa','***') INSERT INTO #TEMP VALUES(2,1,'***','ddd','***') INSERT INTO #TEMP VALUES(2,2,'***','ccc','***') INSERT INTO #TEMP VALUES(2,3,'***','aaa','***') INSERT INTO #TEMP VALUES(3,1,'***','lll','***') INSERT INTO #TEMP VALUES(3,2,'***',NULL,'***') INSERT INTO #TEMP VALUES(3,3,'***','aaa','***') INSERT INTO #TEMP VALUES(4,1,'***','yyy','***') INSERT INTO #TEMP VALUES(4,2,'***','kkk','***') INSERT INTO #TEMP VALUES(4,3,'***','jjjj','***') INSERT INTO #TEMP VALUES(4,4,'***','uuu','***') INSERT INTO #TEMP VALUES(4,5,'***','aaa','***') INSERT INTO #TEMP VALUES(5,1,'***','aaa','***') SELECT MAIN.* FROM #TEMP AS MAIN WHERE EXISTS ( SELECT SUB.* FROM #TEMP AS SUB WHERE SUB.[ID] = MAIN.[ID] AND SUB.[data1] = 'aaa' AND MAIN.[sub-ID] = (SELECT MAX([sub-ID]) FROM #TEMP WHERE [ID] = SUB.[ID] AND [sub-ID] < SUB.[sub-ID] AND [data1] IS NOT NULL) ) ORDER BY MAIN.[ID], MAIN.[sub-ID] ROLLBACK TRANSACTION END TRY BEGIN CATCH PRINT ERROR_MESSAGE() ROLLBACK TRANSACTION END CATCH
-
こんにちは。
どうでしょうかね・・・
with CTE (id, subid) As ( select A.ID, subid = CASE WHEN A.data1 IS NULL THEN A.[sub-ID] - 1 ELSE A.[sub-ID] END from Sample A inner join ( select ID, [sub-ID] - 1 As subid from Sample where data1 = 'aaa' and [sub-ID] != 1) B on A.ID = B.ID and A.[sub-ID] = B.subid where subid != 0 ) select Sample.* from Sample inner join CTE on Sample.ID = CTE.id and Sample.[sub-ID] = cte.subid
- 編集済み いわさ Tak1waMVP, Moderator 2015年3月11日 2:44
-
-
実際には-1してNULLだから、-2してもまだNULLだったら、-3しないといけない、みたいな落ちが付くのでは?と妄想してみました。
WITH Table_Rank as ( select t2.*,row_number() over (Partition by t2.id order by t2.[sub-id] desc ) as rank FROM TABLE t1 INNER JOIN TABLE t2 on t1.id = t2.id and t1.[sub-id] > t2.[sub-id] where t1.data1 = 'AAA' and t2.data1 is not null ) select ID,[sub-id], time, data1, data2 FROM Table_Rank where rank = 1
jzkey
-
了解しました。では、以下のようなSQLを考えてみました。
with CTE as ( select * from IDTEST where data1 = 'aaa' and [sub-ID] > 1 ) select i.* from CTE c inner join IDTEST i on i.ID = c.ID and i.[sub-ID] = (select max([sub-id]) from IDTEST it where it.ID = c.ID and it.[sub-ID] < c.[sub-ID] and it.data1 is not null)
#今気づきましたが、aviator__さんと同じような考え方になりました。aviator__さんは最初から理解されていたんですね!★良い回答には回答済みマークを付けよう! MVP - .NET http://d.hatena.ne.jp/trapemiya/
-
自己結合を使わないで書いてみました。
WITH temp1 AS ( SELECT *, MIN(CASE WHEN data1 = 'aaa' THEN [sub-ID] ELSE null END) OVER (PARTITION BY ID) AS [sub-ID-aaa] FROM Table1 WHERE data1 IS NOT NULL ), temp2 AS ( SELECT *, ROW_NUMBER() OVER (PARTITION BY ID ORDER BY [sub-ID] desc) AS RowNo FROM temp1 WHERE [sub-ID] < [sub-ID-aaa] ) SELECT ID,[sub-id], Time, data1, data2 FROM temp2 WHERE RowNo = 1;
実行の手順は下記のようになります。
1)data1 が null の行を除外
2)各IDグループについて data1 = 'aaa' である最初の sub-ID を求める→これを sub-ID-aaa とする
3)sub-ID が sub-ID-aaa 未満の行を抽出(先頭行が data1 = 'aaa' となっているIDグループはここで除外)
4)各IDグループについて sub-ID の降順に並べる
5)各IDグループの先頭行を抽出
前提として同じIDで data1 = 'aaa' を満たす複数の行がある場合は sub-ID が小さいものを有効にします。
仮にテーブル名を Table1 としています。これを適宜お使いのものに変えてください。
http://systemartlaboratory.com/