SQL Server 結合時に条件ありで一番大きい1件のみデータを抽出したい。

Question

以下の[年度]テーブルと、年度をキーにした[データ]テーブルがあります。
年度ごとに、その年度のデータと、その年度より一つ以上前の最新の1件のデータを取得するようにしたいです。
####[nendo]テーブル

nendo
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
####[data]テーブル
nendo	data
:--	:--:
2	B
4	D
5	E
6	F
9	I
10	J
12	L
####求める結果
nendo	current_data
:--	:--:
1
2	B
3	B
4	D
5	E
6	F
7	F
8	F
9	I
10	J
11	J
12	L
13	L
14	L
15	L
##試したSQL
```SQL
SELECT

nendo.nendo,
x.data AS current_d,
y.data AS before_d

FROM
nendo
LEFT JOIN
data x
ON x.nendo = nendo.nendo
LEFT JOIN
data y
ON y.nendo < nendo.nendo -- ここをどうすればいいかわからない。

データベースはSQLServerです。
急遽仕事を充てられて、細かいバージョンは不明ですので最新のバージョンを想定して回答していただければ問題ありません。
よろしくお願いいたします。

Comments

[退会済みユーザー]

仕事なら組織内で解決するのが筋だと思うのですが？

[退会済みユーザー]

teratailでは個人的な内容の質問しかしてはいけないのですか？

[退会済みユーザー]

組織の業務としてやっていることは組織内で解決するのが当たり前でしょ。あなたに十分な教育を与えられないような組織がどうやって製品品質を担保できるの？

[退会済みユーザー]

IT関係のお仕事をされているかわかりませんが、インターネットを使ってわからないことを調べるということはしませんか？全て組織内で完結しているのですか？それと同じくらいのレベルのことだと思います。
特に今回の質問について、会社の情報が漏洩するような質問ではないのでteratailも問題とはしないと思いますが。

[退会済みユーザー]

あなたの上司、先輩に聞いてみたら？ 厳しい会社だと規則違反で懲戒対象かも。

そもそも、ネットで調べることと、ここのような誰でもアクセスできる Q&A サイトで質問するのは違うって認識できてないのが驚き。

Answer 1

before_dataは外部結合ではなく、スカラサブクエリを使うとよいかと思います。

SELECT 
  N.nendo, 
  D1.data AS current_data,
  (SELECT TOP(1) data 
   FROM data D2
   WHERE D2.nendo < N.nendo 
   ORDER BY D2.nendo DESC) AS before_data
FROM nendo N
LEFT OUTER JOIN data D1 ON N.nendo = D1.nendo

Comments

[退会済みユーザー]

スカラサブクエリというものを初めて知りました。
私が取得したい値通りのクエリでした。

Answer 2

表題と質問内容が一致してないように見えるのは自分だけ？わからんけどこれで。

select A.nendo
  ,(select top 1 C.data from [data] as C where C.nendo=A.nendo) as current_data
  ,(select top 1 B.data from [data] as B where B.nendo=A.nendo+1) as before_data
from nendo as A

Comments

[sazi]

一つ前の年度ではなく、直近の年度ですよ。

[退会済みユーザー]

おっしゃる通り表題と質問内容が一致していませんでしたので修正しました。
直近のデータなのでこのクエリでは取得できませんでした。

[sousuke]

あ、ほんとだ。

Answer 3

SQL Serverでウィンドウ関数を使ってみた。

PARTITION BY でNENDO を指定。

Comments

[sazi]

どのような集合に対して、NENDOでPARTITIONなのでしょう？
直近を求めるのにウインドウ関数だと冗長な気がします。

[Orlofsky]

saziさんのようなサブクエリは思いつきませんでした。