SQLSERVERでinner joinを使ったときの条件分岐

Question

実現したいこと

テーブルXとYがあって、各項目が次のようになっています。

テーブルX
N  A
1  2

テーブルY
M  B C D
1  6 0 2
1  7 2 2
1  8 1 2
2  9 0 0

そこで、次のようなselect文を作りました。

select A * B from X inner join Y on X.N = Y.M

2つのテーブルを結合させて、NとMが一致するBの数字を取得し、
Aと掛け合わせた数字を拾いたいのですが、
Bの数字については、項目Cが一番大きい数字のものだけを
取得したいと考えています。
つまり、2×6や2×8ではなく2×7にしたいと思っています。

また、その一番大きい数字については項目Dに格納されているので、
ここから取得してくることもできます。

これをSQLで実現するためには、どういう構文を書けば良いでしょうか？

Answer 1

実現方法はいろいろあると思いますが、たとえばEXISTSを利用する方法があります。

SELECT X.A * Y1.B
FROM X
INNER JOIN Y Y1 ON X.N = Y1.M
WHERE NOT EXISTS (
  SELECT *
  FROM Y Y2
  WHERE Y1.M = Y2.M
  AND Y1.C < Y2.C
);

個人的にはROW_NUMBERを利用するほうが好みです。

SELECT A * B
FROM (
  SELECT X.A, Y.B, ROW_NUMBER() OVER (PARTITION BY Y.M ORDER BY Y.C DESC) RN
  FROM X 
  INNER JOIN Y ON X.N = Y.M
) T
WHERE RN = 1;

Comments

[morinokuma0621]

ご返答ありがとうございます、試してみます！

[morinokuma0621]

ありがとうございます、思っていた通りのことができるようになりました。
NOT EXISTSでこういう使い方ができるの、初めて知りました。
大変勉強になりました、ありがとうございます！