none
【SQL文】条件の書き方について RRS feed

  • 質問

  • いつもお世話になっております。

    当方、SQLは初心者のため稚拙な質問をお許し下さい。

    LEFT JOINの使い方で質問です。

    例えば、以下のA、Bのテーブルがあるとします。


    ID 名前 時間 

    --------

    1  あ  5       

    ID 判定時間 評価 

    --------

    1  4    × 

    1  8    〇 

    1  6    △

    結合条件はA.ID=B.IDとして、

    A.時間<=B.判定時間のレコードで判定時間が一番最小のレコード1件のみを結合させたいのです。

    上記でいえばBのテーブルの判定時間が6のレコードを結合し、最終的に以下の情報を取得したいです。

    ID 名前 評価 

    --------

    1  あ  △       

    どのようなSQL文を記載すればよいのかわかりません。

    ご教授頂けると幸いです。よろしくお願いします。

    2017年7月30日 13:53

回答

  • こんな?

    SELECT A.ID,A.名前,B.評価 FROM A, B , (SELECT B.ID, MIN(B.判定時間) as 判定時間 FROM B where B.判定時間 >= (SELECT A.時間 FROM A WHERE A.ID = B.ID) GROUP BY B.ID) as C
    WHERE A.ID = B.ID AND B.ID= C.ID AND B.判定時間 = C.判定時間

    個別に明示されていない限りgekkaがフォーラムに投稿したコードにはフォーラム使用条件に基づき「MICROSOFT LIMITED PUBLIC LICENSE」が適用されます。(かなり自由に使ってOK!)

    2017年7月30日 15:16
  • 私からも一つ。

    select top(1) A.ID, A.名前, B.評価
    from A
    inner join B on A.ID = B.ID and A.時間 <= B.判定時間
    order by B.判定時間


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



    2017年7月31日 1:01

すべての返信

  • こんな?

    SELECT A.ID,A.名前,B.評価 FROM A, B , (SELECT B.ID, MIN(B.判定時間) as 判定時間 FROM B where B.判定時間 >= (SELECT A.時間 FROM A WHERE A.ID = B.ID) GROUP BY B.ID) as C
    WHERE A.ID = B.ID AND B.ID= C.ID AND B.判定時間 = C.判定時間

    個別に明示されていない限りgekkaがフォーラムに投稿したコードにはフォーラム使用条件に基づき「MICROSOFT LIMITED PUBLIC LICENSE」が適用されます。(かなり自由に使ってOK!)

    2017年7月30日 15:16
  • gekka 様

    回答ありがとうございます。

    確認してみます。後ほど結果は報告させて頂きます。

    取り急ぎお礼まで。

    2017年7月31日 0:38
  • 私からも一つ。

    select top(1) A.ID, A.名前, B.評価
    from A
    inner join B on A.ID = B.ID and A.時間 <= B.判定時間
    order by B.判定時間


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



    2017年7月31日 1:01
  • trapemiya 様

    回答ありがとうございます。

    頂いた回答で試させて頂きます。

    取り急ぎお礼まで。

    2017年7月31日 1:06
  • TOP(1)なのでID毎の最小時間ではなく、高々1件しか結果が得られませんよ。
    2017年7月31日 2:00
  • >TOP(1)なのでID毎の最小時間ではなく、高々1件しか結果が得られませんよ。

    ご指定ありがとうございます。その通りですね。
    IDはwhereで絞られるものと勝手に思い込んでおりました。もし、IDで絞られるのであれば、以下でいいんですけどね。

    select top(1) A.ID, A.名前, B.評価
    from A
    inner join B on A.ID = B.ID and A.時間 <= B.判定時間
    where A.ID = 1
    order by B.判定時間
    IDで絞り込まないパターンを以下に書いてみましたが、ちょっと複雑になります。
    Bテーブルにサロゲートキーを追加するともうちょっと簡潔になると思うんですけどね。
    with CTE
    as
    (
     select B.ID, min(B.判定時間) B最小判定時間
     from B
     inner join A on A.ID = B.ID and A.時間 <= B.判定時間
      group by B.ID
    )
    select CTE.ID, A.名前,
      (select t.評価 from B t where t.ID = CTE.ID and t.判定時間 = CTE.B最小判定時間) 評価
    from CTE
    inner join A on A.ID = CTE.ID
    #同じ最小の判定時間が複数あって、評価が異なる場合があり得るなら、もう少し工夫が要ります。


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


    • 編集済み trapemiya 2017年7月31日 3:05 追記
    2017年7月31日 3:01
  • もうちょっと簡潔に書けますね。

    select A.ID, A.名前, B.評価
    from A
    inner join B on A.ID = B.ID
    where B.判定時間 = (select top(1) t.判定時間 from B t where t.ID = A.ID and A.時間 <= t.判定時間 order by t.判定時間) 

    ちなみに主キーであるサロゲートキーがあれば、以下の感じになります。以下、BIDが主キーになります。

    select A.ID, A.名前, B.評価
    from A
    inner join B on A.ID = B.ID
    where B.BID = (select top(1) t.BID from B t where t.ID = A.ID and A.時間 <= t.判定時間 order by t.判定時間)
    #SQL ServerはTOP句が使えるので本当に助かります。
    #主キーは基本的に作成することをお勧めします。SQLの作成が楽になったり、Visual StudioでTableAdapterを作成する場合などに必要になることがあります。


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

    2017年7月31日 5:29

  • 津島博士のパフォーマンス講座「第37回 新しいSQLについて」
    http://www.oracle.com/technetwork/jp/database/articles/tsushima/tsushima-hakushi-37-2202125-ja.html
    を見ながらCross Applyを使ってみました :-)

    with A(ID,名前,時間) as(
    select 1,'A',5 union
    select 2,'B',5),
    B(ID,判定時間,評価) as(
    select 1,4, 0 union
    select 1,8,90 union
    select 1,6,50 union
    select 2,6,80)
    select A.ID,A.名前,wk.評価
      from A
    Cross Apply (select top(1) B.評価
                   from B
                  where B.ID = A.ID
                    and A.時間 <= B.判定時間
                 order by B.判定時間) wk
    order by A.ID;
    
    | ID | 名前 | 評価 |
    |----|----|----|
    |  1 |  A | 50 |
    |  2 |  B | 80 |
    

    2017年8月28日 11:51