結合しようとするテーブルのある行の日時が、結合されるテーブルの複数行の連続日時に挟まれた行に結合する検索式

Question

実現したいこと

入室管理をマイクロソフトのsql で管理しているところ、入室をしたユーザーIDと、その際に使ったカードIDと、その時刻の一覧を取得したい。

欲しい出力

secom_id	userid	reg_dt
100	userA	2023-01-10 1:00:00
100	userA	2023-01-20 1:00:00
100	userA	2023-01-30 1:00:00
100	userB	2023-02-10 1:00:00
200	userC	2023-02-10 2:00:00
100	userB	2023-02-20 1:00:00
200	userC	2023-02-21 2:00:00
100	userD	2023-03-10 1:00:00

前提

テーブルの定義

カード利用記録テーブル：t_secom

• セコムカード（以下、単にカードと略します）の入室時刻を記録したテーブルで、列は、カードID、入室時刻があります。
• カードを読み取り機にかざすたびに、カードID、入室時刻がこのテーブルにinsert されます。
• なお、ハードウエア上の制約により、この2つの情報しか取得できません。

テーブル名：t_secom

列	列名	type
カードID	secom_id	int
入室時刻	reg_dt	datetime

データ例

secom_id	reg_dt
100	2023-01-10 1:00:00
100	2023-01-20 1:00:00
100	2023-01-30 1:00:00
100	2023-02-10 1:00:00
200	2023-02-10 2:00:00
100	2023-02-20 1:00:00
200	2023-02-21 2:00:00
100	2023-03-10 1:00:00

カード貸与管理テーブル：m_secom_user

• カードを貸与した日時のテーブルで、列は、カードID、ユーザーID、貸与日時があります。
• カードの貸与があるごとに、カードID、ユーザーID、貸与日時がこのテーブルにinsertされます。
• なお、このテーブルには、貸し出した日時の情報はありますが、返却した日時の情報は持っていません。
• また、カードの返却があると、そのカードは管理者に貸与したという概念で管理しています(つまり、常に誰かにカードを貸与している)。

テーブル名：m_secom_user

列	列名	type
カードID	secom_id	int
ユーザーID	userid	varchar(20)
貸出日時	lending_dt	datetime

データ例

secom_id	userid	lending_dt
100	userA	2023-01-01 1:00:00
100	userB	2023-02-02 2:00:00
200	userC	2023-02-05 5:00:00
100	userD	2023-03-03 3:00:00

発生している問題・該当のソースコード・試したこと

カードを使いまわししていますので、下記のように、単純にテーブル結合をすると、入室時刻に対して複数のユーザーIDが表示されます。

select 
*
from 
t_secom
left join m_secom_user on m_secom_user.secom_id=t_secom.secom_id
and t_secom.reg_dt >m_secom_user.lending_dt

文章で説明すると、結合しようとするテーブル（m_secom_user ）のある行の日時（lending_dt）が、結合されるテーブル（t_secom）の複数行の連続日時（reg_dt）に挟まれた行に結合する検索式が欲しいのですが、これを達成するには、m_secom_userの各行に返却した日時の情報を作り出す方法が必要かと思いますが、この部分が皆目つかないです。

調査したこと

sql 日付とidのテーブル 日付とidとuserのテーブル 結合
こちらのキーワードで検索しましたが、
https://teratail.com/questions/15028?link=qa_related_pc_sidebar
こちらのサイトを見つけることしかできませんでした。
よろしくお願いします。

Comments

[satou]

調査したこと・試したこと
sql 日付とidのテーブル 日付とidとuserのテーブル 結合
こちらのキーワードで検索しましたが、
https://teratail.com/questions/15028?link=qa_related_pc_sidebar
こちらのサイトを見つけることしかできませんでした。

Answer 1

m_secom_userを参照する為のlending_dtを、生成した後に結合するのが効率的でしょう。

select reg.*, usr.userid
from  (
        select  *
              ,(select max(lending_dt) from m_secom_user 
                where secom_id=t_secom.secom_id 
                  and lending_dt <=  t_secom.reg_dt
               ) usr_lending_dt
        from  t_secom
      ) reg
      left join m_secom_user usr
      on    usr.secom_id=reg.secom_id
        and reg.usr_lending_dt = usr.lending_dt

Comments

[satou]

回答ありがとうございます。エラーなく、こちらの希望の検索結果が出ました。

Answer 2

欲しい情報は「元のt_secomに、カラム1列(実際に使用したuserid)を追加したテーブル」です。
そして単純な一致条件ではないので実テーブル同士のjoinでは表現しにくい……ということなので

• 結合するテーブル(useridを含む方)をサブクエリで導出したテーブルにする
• 元のt_secomをセレクトして、相関スカラサブクエリで1カラム(userid)だけ追加する

等で解決できると思います。

---

たとえば相関スカラサブクエリであれば
ただのselect t.* from t_secom as t; にカラム追加するサブクエリとして
「利用記録のとある1行に対して、同一のsecom_id かつ 利用記録の入室時刻よりも古い貸与日時を持つ貸し出し記録の内、最新1件のuserid」となると思います
つまり selec userid from m_secom_user where secom_id=t.secom_id and lending_dt <= t.reg_dt order by lending_dt desc limit 1 みたいなサブクエリをカラムとして追加すればよいと思います（試してないので細かいミスがあったらすみません）

Comments

[satou]

回答ありがとうございます。問題を整理していただき、また、考え方を学ぶことができたと思っています。ありがとうございます。