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

Question

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

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

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

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

Ａ

ＩＤ　名前　時間　

－－－－－－－－

1　　あ　　5　　　　　　　

Ｂ

ＩＤ　判定時間　評価　

－－－－－－－－

1　　4　　　　×　

1　　8　　　　〇　

1　　6　　　　△

結合条件はＡ．ＩＤ＝Ｂ．ＩＤとして、

Ａ．時間＜＝Ｂ．判定時間のレコードで判定時間が一番最小のレコード１件のみを結合させたいのです。

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

ＩＤ　名前　評価　

－－－－－－－－

1　　あ　　△　　　　　　　

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

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

Answer 1

こんな？

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!)

Answer 2

gekka 様

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

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

取り急ぎお礼まで。

Answer 3

私からも一つ。

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/

Answer 4

trapemiya 様

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

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

取り急ぎお礼まで。

Answer 5

TOP(1)なのでID毎の最小時間ではなく、高々１件しか結果が得られませんよ。

Answer 6

＞TOP(1)なのでID毎の最小時間ではなく、高々１件しか結果が得られませんよ。

ご指定ありがとうございます。その通りですね。
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で絞り込まないパターンを以下に書いてみましたが、ちょっと複雑になります。
Ｂテーブルにサロゲートキーを追加するともうちょっと簡潔になると思うんですけどね。

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/

Answer 7

もうちょっと簡潔に書けますね。

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/

Answer 8

津島博士のパフォーマンス講座「第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 |