none
SQL 文の書き方 RRS feed

  • 質問

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

    現在 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
    2015年3月11日 2:01

回答

  • 綺麗かどうかは別として

    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
    • 回答の候補に設定 星 睦美 2015年3月16日 5:48
    • 回答としてマーク 星 睦美 2015年3月19日 8:01
    2015年3月11日 2:38
  • 実際には-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

    • 回答の候補に設定 星 睦美 2015年3月16日 5:48
    • 回答としてマーク 星 睦美 2015年3月19日 8:01
    2015年3月11日 4:39
  • 了解しました。では、以下のような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/

    • 回答の候補に設定 星 睦美 2015年3月16日 5:48
    • 回答としてマーク 星 睦美 2015年3月19日 8:01
    2015年3月11日 4:40
  • 自己結合を使わないで書いてみました。
    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/


    • 編集済み 三輪の牛 2015年3月14日 14:57 補足
    • 回答の候補に設定 星 睦美 2015年3月16日 5:48
    • 回答としてマーク 星 睦美 2015年3月19日 8:01
    2015年3月14日 14:49

すべての返信

  • 綺麗かどうかは別として

    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
    • 回答の候補に設定 星 睦美 2015年3月16日 5:48
    • 回答としてマーク 星 睦美 2015年3月19日 8:01
    2015年3月11日 2:38
  • こんにちは。

    どうでしょうかね・・・

    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

    2015年3月11日 2:41
    モデレータ
  • ありがとうございます、参考にして作ってみたいと思います。
    2015年3月11日 2:56
  • ありがとうございます、参考にして作ってみたいと思います。
    2015年3月11日 2:56
  • 確認です。

    >ただし、その行の 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/

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

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

    2015年3月11日 3:38
  • 実際には-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

    • 回答の候補に設定 星 睦美 2015年3月16日 5:48
    • 回答としてマーク 星 睦美 2015年3月19日 8:01
    2015年3月11日 4:39
  • 了解しました。では、以下のような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/

    • 回答の候補に設定 星 睦美 2015年3月16日 5:48
    • 回答としてマーク 星 睦美 2015年3月19日 8:01
    2015年3月11日 4:40
  • なるほど。
    jzkeyさんが仰っているように-2してまだNULLだった場合に-3しないといけない、
    の場合は私のクエリはNGですね…。
    御三方が提示されているクエリが正しいです。

    表面的なところしか見ていなかった(反省)
    2015年3月11日 5:00
    モデレータ
  • みなさん、ありがとうございます。

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

    2015年3月11日 6:29
  • 自己結合を使わないで書いてみました。
    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/


    • 編集済み 三輪の牛 2015年3月14日 14:57 補足
    • 回答の候補に設定 星 睦美 2015年3月16日 5:48
    • 回答としてマーク 星 睦美 2015年3月19日 8:01
    2015年3月14日 14:49