mySQLの履歴テーブルからのデータ取得について

Question

実現したいこと/不明点

「投稿テーブル」と「ユーザーテーブル（履歴）」があり、投稿日時点でのユーザー情報を取得したい。
最新データの取得であればmax関数を使えばできるということはわかっていますが、投稿日時点の履歴をどのように引っ張ってこればよいかわからない状態です。

テーブル情報

投稿テーブル

id|user_id|created
1 |A      |20200621
2 |A      |20200505
3 |B      |20200520
3 |B      |20200608

ユーザー履歴テーブル

user_id|rank|created
A      |1   |20200503
A      |1   |20200601
A      |3   |20200615
A      |2   |20200701
B      |2   |20200516
B      |1   |20200601
B      |3   |20200607
B      |5   |20200701

ほしい結果

id|user_id|rank|投稿created
1 |A      |3   |20200621
2 |A      |1   |20200505
3 |B      |2   |20200520
3 |B      |3   |20200608

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

どのようにQueryを記載すればよいかご教授いただけますと幸いです

Comments

[aokikenichi]

「投稿日時点の履歴」とな何でしょうか
投稿テーブルとユーザー履歴テーブルの created はどう違いますか

A 3 20200615 → 20200621
A 1 20200601 → 20200505

の関係性が分かりません。

[m.ts10806]

テーブル定義を提示してください（CREATE TABLE文で）

[nakashima]

投稿テーブルのcreatedはユーザーがある情報を投稿した日時になります。
ユーザー履歴テーブルのcreatedはユーザー情報に追加があった日時になります。

今回取得したい情報は、ユーザーがある情報を投稿した日時時点でのユーザー情報（rank）を取得したいです

Answer 1

テーブル定義はyambejp氏のを拝借。ありがとナス。本当は質問者さんに用意してほしい…

本題、rankだけでいいなら

select t1.id,t1.user_id
  ,(select t3.rank
    from rireki t3
    where t3.user_id=t1.user_id
    and t3.created < t1.created
    order by t3.created desc
    limit 1) rank
  ,t1.created 投稿created
from toukou t1

こう。

rirekiテーブルのなにがしかもほしいなら無理やり一発で

select t1.id,t1.user_id,t2.rank,t1.created 投稿created,t2.created t2_created
from toukou t1
  inner join rireki t2 on t2.user_id = t1.user_id
    and t2.created = (select t3.created
                      from rireki t3
                      where t3.user_id=t1.user_id
                        and t3.created < t1.created
                      order by t3.created desc
                      limit 1)
order by id

こう。

Answer 2

Aは20200505時点、Bは20200520時点で履歴がないので
「ほしい結果」を導くことはできません

Comments

[nakashima]

ご指摘ありがとございます。データを修正いたしました。

[yambejp]

ちょっと冗長ですが・・・
create table toukou(id int,user_id varchar(10),created date);
insert into toukou values
(1,'A','20200621'),
(2,'A','20200505'),
(3,'B','20200520'),
(3,'B','20200608');

create table rireki (user_id varchar(10),rank int,created date,unique user_id_created(user_id,created));
insert into rireki values
('A',1,'20200503'),
('A',1,'20200601'),
('A',3,'20200615'),
('A',2,'20200701'),
('B',2,'20200516'),
('B',1,'20200601'),
('B',3,'20200607'),
('B',5,'20200701');

[yambejp]

select
min(t1.id) as id,
t1.user_id,
t1.created as t1_created,
max(t2.created) as t2_created,
(select rank from rireki where user_id=t1.user_id and created=max(t2.created)) as rank
from toukou as t1
inner join rireki t2
on t1.user_id=t2.user_id
and t1.created>=t2.created
group by user_id,t1_created
order by id,rank