TIME型同士の比較を含むSQLの書き方について

Question

MYSQLの経験値が浅いこともあり、MYSQLのSQLの書き方について、困っております。
恐れ入りますが、解決方法がわかるようでしたら、ご教示頂けますと助かります。

前提・実現したいこと

・MYSQL5.7を使っています。
・以下の要件を1つのSQL文で実現したいです。

①table_Bから選手名を条件に最も早い記録(min(record))とその大会日付を抽出する。
②table_Aのベストタイムとtable_Bの最も早い記録(min(record))を比較する。
③比較した結果、table_Bの記録が速い場合はtable_Aのrecord、dayを更新する。
table_Aの記録が速い場合は更新しない。

ex) 佐藤太郎
①："0:00:09.99"、"2020/3/20"を抽出する。
②：【table_A 】"0:00:10.22"　＞　【table_B 】　"0:00:09.99"
③：【table_A 】のrecordを"0:00:09.99"、dayを"2020/3/20"に更新する。

発生している課題

・上記②で異なるテーブルのrecord（TIME型 time(2)）を比較する場合にSQLの書き方がわかっておりません。
・①、②、③をそれぞれ分けたSQLは書けますが、1つにする方法がわかっておりません。

補足情報（FW/ツールのバージョンなど）

SQLを１つにまとめるのはこの後に、アプリに利用するためです。

Comments

[sazi]

table_Aは自己ベストなのに複数件あるのは何故ですか？

[hoshi-takanori]

太郎さんと次郎さんだからですね。（紛らわしいので姓も変えた方が分かりやすいと思います。）

ところで、自己ベストは大会記録の中から選ばれるんでしょうか？ それなら、テーブルじゃなくてビューで良いのでは？

[teltelboy]

コメントありがとうございます。

＞table_Aは自己ベストなのに複数件あるのは何故ですか？
hoshi-takanori様のコメント通りになります。紛らわしいので、資料を修正しました。
（変更前）佐藤次郎　⇒（変更後）田中花子

>ところで、自己ベストは大会記録の中から選ばれるんでしょうか？ それなら、テーブルじゃなくてビューで良いのでは？

自己ベストは大会記録から選ばれますので、ビューによる実装も可能です。この考えは私自身なかったので、ビュー実装を検討させていただきました。
デメリットとして、このビューが呼ばれる回数が非常に多くなることが想定されるため、性能面がどうしても気になってしまいます。ですので、できれば、自己ベストテーブルに事前に格納しておきたいです。

※呼ばれる回数の点は事前の説明足りてなかったですが、選手数が非常に多いことと、自己ベストを表示する画面を頻繁に使うためです。
ご不明な点等ございましたら、ご連絡いただければと思います。

Answer 1

SQL作成してみました

update table_A t0,
(select t1.* from table_B t1 join
  (select name, kyori, min(record) record from table_B group by 1,2) t2 
   on t1.name=t2.name and t1.record=t2.record and t1.kyori=t2.kyori
) t3 
set t0.record=t3.record, t0.day=t3.day
where t0.name=t3.name and t0.kyori=t3.kyori
and t0.record>t3.record
;

簡単な名前・距離指定から

update table_A t0,
(select t1.* from table_B t1 join
  (select name, kyori, min(record) record from table_B group by 1,2) t2 
   on t1.name=t2.name and t1.record=t2.record and t1.kyori=t2.kyori
) t3 
set t0.record=t3.record, t0.day=t3.day
where t0.name=t3.name and t0.kyori=t3.kyori
and t0.record>t3.record
and (t0.name,t0.kyori)=('佐藤 太朗','100M')
;

Comments

[teltelboy]

ご回答下さり、ありがとうございます。
table_Aの全行を更新する形のSQLの書き方について、勉強になりました。

すみません、私の質問の仕方が悪かったのですが、
今回はtable_Aの1行単位に上記①②③を1つのSQLで実現したいと考えております。
具体的には、"佐藤太郎"だけに絞って、上記①②③を1つのSQLで書く方法をご教示いただけないでしょうか。

[amura]

table_Aの１行が決まる条件を教えていだだきますでしょうか？例えば連番が有ると良いのですが。名前指定でも良いです。

[teltelboy]

table_Aは選手名(name)、距離(kyori)で行が一意になるようにしております。
※連番振らないと、書けない場合は連番を追加することも可能です。
お手数ですが、ご確認いただけますと助かります。

[amura]

RDBは基本的に順番を持っていないため常に同じものが一番目にselectされる事を保証していません。ですのでテーブル作成時に連番を振る事で対応する方法が有ります。create tableに id MEDIUMINT NOT NULL AUTO_INCREMENT,　を加えることが出来、この番号を条件に加えます。
他には、選手名と距離が指定できるならsqlの条件に加える事もできます。

Answer 2

INSERT ... ON DUPLICATE KEY UPDATE 構文を使用してupsert(あれば更新無ければinsert)すれば、table_aを常に最新の状態にできます。
その為には一意キーを明確にする必要がありますが。

Comments

[teltelboy]

ご回答ありがとうございます。
参考にさせていただきます。