自己結合で結合先の1レコードのみを取得したい。

Question

競馬のデータを整理していて、SQLの自己結合の方法が解らず相談します。
SQL Server 2017 を使用しています。TransactSQLです。

目的は、ある馬が直前に出走した日付のレースを特定する事です。

テーブルは以下の通りです。

feature (予想テーブル)
race_id int
horse_id int

race_info(レース情報テーブル)
race_id int
date varchar(10)

あるレースの日付を取得したい場合は、以下のように行います。

select f.horse_id,f.race_id,ri.date
from feature f inner join race_info ri on f.race_id = ri.race_id

こんなかんじのデータが取得できます。

horse_id	race_id	date
2004100525	1	2006-08-12

では、あるレースの直前のレースの日付を取得する場合にはどうすれば良いでしょうか？
このようなデータを取得できたら理想的です。

horse_id	race_id	date	race_id2	date2
2004100525	1	2006-08-12	10	2006-03-11

inner join でもう一つずつfeature とrace_infoを結合する事はできるのですが、日付を > しても、直前のレースだけでなく過去の全てのレースが検索されてしまいます。

MySQLであれば、on句でlimit 1 とかすれば、1レコードだけ取れる気がします。しかしTransactSQLにはありません。
サブクエリでどうにかすればいいかと思ったのですが、サブクエリ内で括弧の外側のテーブルの情報は条件に指定できませんので条件が指定できません。
仮にサブクエリ内からどうにか条件を指定できたとしても、1行のみ取得する方法がわかりません。top 1を使うと、そのテーブルの検索結果の先頭1行しか取得できません。

どうすれば良いのでしょうか。

Comments

[yambejp]

sampleデータがあると回答しやすいのですが・・・
ヒットさせたいデータと、させたくないデータが
わかるようなものがベター

[usugita_san]

すみません、解決しちゃいました…。次回からできるだけサンプルデータをつけるようにします。お手数をおかけしました。

Answer 1

apply句を使用すればよろしいかと思います。そのような場合に使います。
条件読み取って再度追記してみました。

select f.horse_id,f.race_id,ri.date,X.race_id2,X.date2
from feature f
  inner join race_info ri on f.race_id = ri.race_id
  outer apply (
    select top 1 ri2.race_id as race_id2,ri2.date as date2
    from feature f2
      inner join race_info ri2 on ri2.race_id = f2.race_id
    -- 条件は質問文からはうまく読み取れないのでなんとなく
    where f2.horse_id=f.horse_id
      and ri2.date<ri.date
    -- 並び順指定、この並び順から先頭１件を取得できる
    order by ri2.date desc
  ) as X

追記

「サブクエリ内で括弧の外側のテーブルの情報は条件に指定できません」これが引っかかるのですが
select側に記述した場合カッコの外側の値を使って指定できます。
複数項目持ってくるとエラーになるのですが、項目が１つの場合ならサブクエリで処理できるはずです。

select f.horse_id,f.race_id,ri.date
  ,(
    select top 1 ri2.date--,ri2.race_id  ←これはエラー
    from feature f2
      inner join race_info ri2 on ri2.race_id = f2.race_id
    -- 条件は質問文からはうまく読み取れないのでなんとなく
    where f2.horse_id=f.horse_id
      and ri2.date<ri.date
    -- 並び順指定、この並び順から先頭１件を取得できる
    order by ri2.date desc
  ) as date2
from feature f
  inner join race_info ri on f.race_id = ri.race_id

Answer 2

その日付より小さくて、最大の日付をひろうか
where exists系の副問合せをするかだと思います