質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

ただいまの
回答率

89.10%

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

解決済

回答 2

投稿

  • 評価
  • クリップ 0
  • VIEW 276

usugita_san

score 234

競馬のデータを整理していて、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行しか取得できません。

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

  • 気になる質問をクリップする

    クリップした質問は、後からいつでもマイページで確認できます。

    またクリップした質問に回答があった際、通知やメールを受け取ることができます。

    クリップを取り消します

  • 良い質問の評価を上げる

    以下のような質問は評価を上げましょう

    • 質問内容が明確
    • 自分も答えを知りたい
    • 質問者以外のユーザにも役立つ

    評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。

    質問の評価を上げたことを取り消します

  • 評価を下げられる数の上限に達しました

    評価を下げることができません

    • 1日5回まで評価を下げられます
    • 1日に1ユーザに対して2回まで評価を下げられます

    質問の評価を下げる

    teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。

    • プログラミングに関係のない質問
    • やってほしいことだけを記載した丸投げの質問
    • 問題・課題が含まれていない質問
    • 意図的に内容が抹消された質問
    • 過去に投稿した質問と同じ内容の質問
    • 広告と受け取られるような投稿

    評価が下がると、TOPページの「アクティブ」「注目」タブのフィードに表示されにくくなります。

    質問の評価を下げたことを取り消します

    この機能は開放されていません

    評価を下げる条件を満たしてません

    評価を下げる理由を選択してください

    詳細な説明はこちら

    上記に当てはまらず、質問内容が明確になっていない質問には「情報の追加・修正依頼」機能からコメントをしてください。

    質問の評価を下げる機能の利用条件

    この機能を利用するためには、以下の事項を行う必要があります。

質問への追記・修正、ベストアンサー選択の依頼

  • yambejp

    2019/09/02 11:17 編集

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

    キャンセル

  • usugita_san

    2019/09/02 11:50

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

    キャンセル

回答 2

checkベストアンサー

+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
    -- 並び順指定、この並び順から先頭1件を取得できる
    order by ri2.date desc
  ) as X

追記

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

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
    -- 並び順指定、この並び順から先頭1件を取得できる
    order by ri2.date desc
  ) as date2
from feature f
  inner join race_info ri on f.race_id = ri.race_id

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

0

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

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

15分調べてもわからないことは、teratailで質問しよう!

  • ただいまの回答率 89.10%
  • 質問をまとめることで、思考を整理して素早く解決
  • テンプレート機能で、簡単に質問をまとめられる