SQL 文の書き方

Question

はじめてここを利用させていただきます。
投稿が適切でないようであればご指摘いただければと思います。

現在 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    ***

Answer 1

綺麗かどうかは別として

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

Answer 2

こんにちは。

どうでしょうかね・・・

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

Answer 3

ありがとうございます、参考にして作ってみたいと思います。

Answer 4

ありがとうございます、参考にして作ってみたいと思います。

Answer 5

確認です。

＞ただし、その行の data1 が NULL の場合は 2 を引いた行を抽出、sub-ID が 1 の場合には抽出しないという条件をつけたいです。

ということですが、

＞※ (3,2) は data1 に NULL があるので (3,1) を抽出

は、(3,0)を抽出することにならないのでしょうか？　また、抽出データのIDは、data1がaaaのデータのIDと同じという条件でよろしいのでしょうか？

---

★良い回答には回答済みマークを付けよう！ MVP - .NET 　http://d.hatena.ne.jp/trapemiya/

Answer 6

すみません、説明が分かりづらかったかもしれません。
意図としては data1 が "aaa" で、その 1 個手前の埋まっているデータを取りたいので、
NULL の場合はその行は飛ばして (3,1) のつもりでした。(3,3) の sub-ID から -2 ですね。

sub-ID が 0 のデータはないので、"aaa" の行の sub-ID が 1 の場合には処理しないということになります。

Answer 7

実際には－１してNULLだから、－２してもまだNULLだったら、－３しないといけない、みたいな落ちが付くのでは？と妄想してみました。

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

Answer 8

了解しました。では、以下のような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/

Answer 9

なるほど。
jzkeyさんが仰っているように-2してまだNULLだった場合に-3しないといけない、
の場合は私のクエリはNGですね…。
御三方が提示されているクエリが正しいです。

表面的なところしか見ていなかった（反省）

Answer 10

みなさん、ありがとうございます。

知らない構文などもあったので調べながらやってみます。

Answer 11

自己結合を使わないで書いてみました。

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;

　実行の手順は下記のようになります。
１）data1 が null の行を除外
２）各IDグループについて data1 = 'aaa' である最初の sub-ID を求める→これを sub-ID-aaa とする
３）sub-ID が sub-ID-aaa 未満の行を抽出(先頭行が data1 = 'aaa' となっているIDグループはここで除外)
４）各IDグループについて sub-ID の降順に並べる
５）各IDグループの先頭行を抽出
　前提として同じIDで data1 = 'aaa' を満たす複数の行がある場合は sub-ID が小さいものを有効にします。
　仮にテーブル名を Table1 としています。これを適宜お使いのものに変えてください。

---

http://systemartlaboratory.com/