none
ストアド RRS feed

  • 質問

  • ご教授お願い致します。

    ストアドでAテーブルとBテーブルをA left outer join Bとして外部結合し

    A.test項目 = B.test項目で結合しています。

    このA.testですが、データが存在しない場合もあります。

    よって、結合表でみるとB.test項目がnullで表記されます。

    しかし、Bテーブル単体では、test項目はnullを有効にしていません。

    また、このストアドにパラメータとして@test下限と@test上限および

    キーワード一致ようして@testキーワードも設定しています。

    そして、このパラメータと結合表から

    select  A.test,B.test

    from 結合表

    where between @test下限 and @test上限

         and B.test like '%@test%'キーワード%'を

    指定しました。

    しかしながら、この範囲を全てフリーにして抽出したら

    nullのレコード以外(A.testにデータがある)しか抽出されませんでした。

    誠に、お手数ですがこのストアドが成立する処置をお願い致します。

    また、このwhere句に

    where between @test下限 and @test上限 or B.test is null

         and B.test like '%@test%'キーワード%' or B.test is nullを

    追加したら、キーワードを入れた場合にキーワードとnullが出てしまいました。

    以上

    2011年6月10日 7:40

回答

  • ストアドプロシージャなのでSQL文を組み立てた方が確実かつ応用が効くと思います。以下に例を示しますが、コード未検証です。

    CREATE PROCEDURE 抽出テスト
      @test上限    nvarchar(50),
      @test下限    nvarchar(50),
      @test      nvarchar(50)
    AS
    BEGIN
      SET NOCOUNT ON;
      
      declare @sqlwk   nvarchar(max);
      declare @wherewk  nvarchar(max);
      declare @testwk  nvarchar(max);
    
      set @sqlwk = 'select A.test, B.test from dbo.Aテーブル A
                         left outer join dbo.Bテーブル B on A.test項目 = B.test項目 ';
      set @wherewk = '';
      
      if @test上限 != '' or @test下限 != '' set @wherewk = 'B.test between @test上限 and @test下限 and ';
      
      if @test != ''
      begin
        set @wherewk += 'B.test like @testwk and ';
        set @testwk = '%' + @test + '%'
      end
      
      --最後の「 and 」を除く。LENは最後の空白をカウントしない。
      if @wherewk != ''
        set @wherewk = 'where ' + substring(@wherewk, 1, len(@wherewk) - 4);
    
      set @sqlwk += @wherewk;
        
      exec sp_executesql @sqlwk, N'@test上限 nvarchar(50), @test下限 nvarchar(50), @testwk nvarchar(50)', @test上限, @test下限, @testwk
      
    END

    ★良い回答には回答済みマークを付けよう! わんくま同盟 MVP - Visual C# http://d.hatena.ne.jp/trapemiya/
    • 回答としてマーク zeak01 2011年6月15日 23:56
    2011年6月13日 5:40

すべての返信

  • ストアドでAテーブルとBテーブルをA left outer join Bとして外部結合し

    A.test項目 = B.test項目で結合しています。

    このA.testですが、データが存在しない場合もあります。

    よって、結合表でみるとB.test項目がnullで表記されます。

     データが存在しないとは、A.test項目の値に該当する値がB.test項目に無いということでしょうか?でれば、外部結合ですのでB.test項目がnullになるのは正しい結果です。

    しかし、Bテーブル単体では、test項目はnullを有効にしていません。

    外部結合の結果、Bテーブルに該当するレコードが無かったためnullと表示されているのだと思います。test項目にnullを許可することとは全く関係ありません。

    しかしながら、この範囲を全てフリーにして抽出したら

    nullのレコード以外(A.testにデータがある)しか抽出されませんでした。

     where句を除いたということでしょうか? であれば、結合表そのものになるはずですが・・・

    where between @test下限 and @test上限 or B.test is null

         and B.test like '%@test%'キーワード%' or B.test is nullを

    追加したら、キーワードを入れた場合にキーワードとnullが出てしまいました。

    andとorには結合の優先順位があります。必要であればカッコで括るようにして下さい。

     #ちなみにbetweenの書き方がおかしく、このままだと動かないですよね?


    ★良い回答には回答済みマークを付けよう! わんくま同盟 MVP - Visual C# http://d.hatena.ne.jp/trapemiya/
    • 編集済み trapemiya 2011年6月10日 8:18 betweenについて追記
    2011年6月10日 8:12
  •  ご回答ありがとうございます。

     また、当方の言葉足らずで申し訳ありません。

     文章で表現するとうまく伝わらないので

     下に図で表現したいと思います。

     例えば、作成された結合表が下記のようになったとします。

     行  A.test  B.test(他にも列を取得していますが省力しています。)

     1    abc      abc

     2    xyz      xyz

     3   null   null  (←Nullができあがる)

    で、以下の抽出条件を加えると、1、2行しか抽出されない。

    where B.test between @test下限 and @test上限  ←where行1(初めの質問分で誤記がありました。こちらで行っています)

    and   B.test like '%@test%'                ←where行2

    よって、where行2に or  B.test is nullを追加したら

    1~3行全て抽出はされたのですが

    今度は、@testにキーワード(例:y)を入れて実行したらキーワードが考慮されず

    1~3行全て抽出されました。(希望は、2行目のみ抽出されたい)

    つきましては、条件通りに抽出出来る方法をお願いします。

    以上

    2011年6月13日 1:21
  • よって、where行2に or  B.test is nullを追加したら

    1~3行全て抽出はされたのですが

     

     or  B.test is nullを追加したら、他の条件がどうであろうとB.testがnullの行は必ず抽出されることになります。

    今度は、@testにキーワード(例:y)を入れて実行したらキーワードが考慮されず

    1~3行全て抽出されました。(希望は、2行目のみ抽出されたい) 

     1~3行全て抽出された時は@testが空文字の時ですよね? 例にあるようにyを指定した場合は、2行目(xyz)と3行目(null)の2行が抽出されている状態ですよね? それともyを指定した時に1~3行全てが抽出されているのでしょうか?

    >。(希望は、2行目のみ抽出されたい) 

    とありますが、2行目のみの抽出はxyzの1行であり、nullである行は抽出したくないということでしょうか?

    全体的にわかりにくのはnullである行をどう抽出されたいかということです。ある時はnullである行を抽出し、ある時はnullである行を抽出したくないのでしょうか?

     


    ★良い回答には回答済みマークを付けよう! わんくま同盟 MVP - Visual C# http://d.hatena.ne.jp/trapemiya/
    2011年6月13日 3:08
  •   早速のご回答ありがとうございます。

     わかりにくいぶぶんについて改めて回答します。

     >>1~3行全て抽出された時は@testが空文字の時ですよね? 例にあるようにyを指定した場合は、2行目(xyz)と3行目(null)の2行が抽出されている状態ですよね? それともyを指定した時に1~3行全てが抽出されているのでしょうか? 

     →こちらは、@testは空文字です。1~3行全てが表示されてしまいます。(例ですとyにヒットするレコードのみ抽出してほしい) 

    >>。(希望は、2行目のみ抽出されたい) 

        とありますが、2行目のみの抽出はxyzの1行であり、nullである行は抽出したくないということでしょうか?

    →文面がわかりにくく申し訳ありません。

     改めて、説明したいことをお話しすると

     範囲を指定しない場合は、全件抽出してほしい。(例ですとnullも含めて1~3行全て)

     範囲を指定した場合は、指定した範囲内のレコードを抽出してほしい。(例ですと@testにyと入力したら2行目のみ抽出)

     以上です。

     

    2011年6月13日 4:00
  • ストアドプロシージャなのでSQL文を組み立てた方が確実かつ応用が効くと思います。以下に例を示しますが、コード未検証です。

    CREATE PROCEDURE 抽出テスト
      @test上限    nvarchar(50),
      @test下限    nvarchar(50),
      @test      nvarchar(50)
    AS
    BEGIN
      SET NOCOUNT ON;
      
      declare @sqlwk   nvarchar(max);
      declare @wherewk  nvarchar(max);
      declare @testwk  nvarchar(max);
    
      set @sqlwk = 'select A.test, B.test from dbo.Aテーブル A
                         left outer join dbo.Bテーブル B on A.test項目 = B.test項目 ';
      set @wherewk = '';
      
      if @test上限 != '' or @test下限 != '' set @wherewk = 'B.test between @test上限 and @test下限 and ';
      
      if @test != ''
      begin
        set @wherewk += 'B.test like @testwk and ';
        set @testwk = '%' + @test + '%'
      end
      
      --最後の「 and 」を除く。LENは最後の空白をカウントしない。
      if @wherewk != ''
        set @wherewk = 'where ' + substring(@wherewk, 1, len(@wherewk) - 4);
    
      set @sqlwk += @wherewk;
        
      exec sp_executesql @sqlwk, N'@test上限 nvarchar(50), @test下限 nvarchar(50), @testwk nvarchar(50)', @test上限, @test下限, @testwk
      
    END

    ★良い回答には回答済みマークを付けよう! わんくま同盟 MVP - Visual C# http://d.hatena.ne.jp/trapemiya/
    • 回答としてマーク zeak01 2011年6月15日 23:56
    2011年6月13日 5:40
  • クエリ例のサンプルまで頂きありがとうございます。

    解読して、使用出来るようにしてみたいと思います。

    ありがとうございます。

    2011年6月15日 23:57