クエリについて2


2つのテーブルを特定の期間におけるレコードでjoinをしようとしています。  
joinするtable_A
をevent_codeで連結したいのですが、table_Bのevent_timeより過去日での最新日付を持ったevent_codeでjoinさせたいです。

テーブルの仕様は以下です。

table_A

event_code | reg_time | description  
---|---|---  
apple | 2022-08-01 00:00:00 | it's apple  
orange | 2022-08-01 00:00:00 | it's orange  
grape | 2022-08-01 00:00:00 | it's grape  
apple2 | 2022-09-01 00:00:00 | big apple  
orange | 2022-09-01 00:00:00 | big orage  
grape | 2022-09-01 00:00:00 | big grape  
ice | 2022-10-01 00:00:00 | big ice  
  
table_B

event_code | event_time | user_id  
---|---|---  
apple | 2022-08-10 00:00:00 | 0001  
orange | 2022-08-21 00:00:00 | 0001  
grape | 2022-08-30 00:00:00 | 0001  
apple2 | 2022-09-11 00:00:00 | 0001  
orange | 2022-09-21 00:00:00 | 0001  
grape | 2022-09-31 00:00:00 | 0001  
apple2 | 2022-10-11 00:00:00 | 0001  
ice | 2022-10-15 00:00:00 | 0001  
orange | 2022-10-21 00:00:00 | 0001  
grape | 2022-10-30 00:00:00 | 0001  
  
join後の期待する結果は以下の通りです。  
SQLクエリで書けますでしょうか？

event_code | event_time | user_id | reg_time | description  
---|---|---|---|---  
apple | 2022-08-10 00:00:00 | 0001 | 2022-08-01 00:00:00 | it's apple  
orange | 2022-08-21 00:00:00 | 0001 | 2022-08-01 00:00:00 | it's orange  
grape | 2022-08-30 00:00:00 | 0001 | 2022-08-01 00:00:00 | it's grape  
apple2 | 2022-09-11 00:00:00 | 0001 | 2022-09-01 00:00:00 | big apple  
orange | 2022-09-21 00:00:00 | 0001 | 2022-09-01 00:00:00 | big orage  
grape | 2022-09-31 00:00:00 | 0001 | 2022-09-01 00:00:00 | big grape  
apple2 | 2022-10-11 00:00:00 | 0001 | 2022-09-01 00:00:00 | big apple  
ice | 2022-10-15 00:00:00 | 0001 | 2022-10-01 00:00:00 | big ice  
orage | 2022-10-21 00:00:00 | 0001 | 2022-09-01 00:00:00 | big orange  
grape | 2022-10-30 00:00:00 | 0001 | 2022-09-01 00:00:00 | big grape  
  
テーブル作成のコードは以下の通りです。

[code]

    CREATE or replace TABLE table_A 
        (event_code  VARCHAR(10),
         reg_time    DATETIME,
         description VARCHAR(15));
    
    INSERT INTO table_A
    VALUES
      ('apple',  '2022-08-01 00:00:00', 'it''s apple'),
      ('orange', '2022-08-01 00:00:00', 'it''s orange'),
      ('grape',  '2022-08-01 00:00:00', 'it''s grape'),
      ('apple2', '2022-09-01 00:00:00', 'big apple'),
      ('orange', '2022-09-01 00:00:00', 'big orage'),
      ('grape',  '2022-09-01 00:00:00', 'big grape'),
      ('ice',  '2022-10-01 00:00:00', 'big ice');
    
    CREATE or replace TABLE table_B
        (event_code VARCHAR(10),
         event_time DATETIME,
         user_id    VARCHAR(5));
    
    INSERT INTO table_B
    VALUES
      ('apple',  '2022-08-10 00:00:00', '0001'),
      ('orange', '2022-08-21 00:00:00', '0001'),
      ('grape',  '2022-08-30 00:00:00', '0001'),
      ('apple2', '2022-09-11 00:00:00', '0001'),
      ('orange', '2022-09-21 00:00:00', '0001'),
      ('grape',  '2022-09-30 00:00:00', '0001'),
      ('apple2', '2022-10-11 00:00:00', '0001'),
      ('ice',  '2022-10-15 00:00:00', '0001'),
      ('orange', '2022-10-21 00:00:00', '0001'),
      ('grape',  '2022-10-30 00:00:00', '0001'),
    ;
    
[/code]




payanecoさんのDDL/初期データを使用したMySQL8.0.27での別解です。

[code]

    select
      B.event_code,
      B.event_time,
      B.user_id,
      A1.reg_time
    from
      table_A as A1
      inner join table_B as B
        on A1.event_code=B.event_code
        and A1.reg_time<B.event_time
      left join table_A as A2
        on A2.event_code=B.event_code
        and A2.reg_time<B.event_time
        and A2.reg_time>A1.reg_time -- A1.regtimeが最大のときこの条件を満たすA2レコードはない
    where
      A2.event_code is NULL
    ;
    
[/code]




where句の副問い合わせを使って、table_Aのreg_timeをtable_Bのevent_timeより過去日での最新日付に限定することで実現できます。

**Schema (MySQL v8.0)**

[code]

    CREATE TABLE table_A
        (event_code  VARCHAR(10),
         reg_time    DATETIME,
         description VARCHAR(15));
    
    INSERT INTO table_A
    VALUES
      ('apple',  '2022-08-01 00:00:00', 'it''s apple'),
      ('orange', '2022-08-01 00:00:00', 'it''s orange'),
      ('grape',  '2022-08-01 00:00:00', 'it''s grape'),
      ('apple2', '2022-09-01 00:00:00', 'big apple'),
      ('orange', '2022-09-01 00:00:00', 'big orage'),
      ('grape',  '2022-09-01 00:00:00', 'big grape'),
      ('ice',  '2022-10-01 00:00:00', 'big ice');
    
    CREATE TABLE table_B
        (event_code VARCHAR(10),
         event_time DATETIME,
         user_id    VARCHAR(5));
    
    INSERT INTO table_B
    VALUES
      ('apple',  '2022-08-10 00:00:00', '0001'),
      ('orange', '2022-08-21 00:00:00', '0001'),
      ('grape',  '2022-08-30 00:00:00', '0001'),
      ('apple2', '2022-09-11 00:00:00', '0001'),
      ('orange', '2022-09-21 00:00:00', '0001'),
      ('grape',  '2022-09-30 00:00:00', '0001'),
      ('apple2', '2022-10-11 00:00:00', '0001'),
      ('ice',  '2022-10-15 00:00:00', '0001'),
      ('orange', '2022-10-21 00:00:00', '0001'),
      ('grape',  '2022-10-30 00:00:00', '0001');
    
[/code]

* * *

**Query #1**

[code]

    select B.event_code,
           B.event_time,
           B.user_id,
           A.reg_time
    from   table_A as A,table_B as B
    where  A.event_code = B.event_code
    and    A.reg_time   = (select max(A2.reg_time)
                           from   table_A as A2
                           where  A2.event_code = B.event_code
                           and    A2.reg_time < B.event_time);
    
[/code]

event_code | event_time | user_id | reg_time  
---|---|---|---  
apple | 2022-08-10 00:00:00 | 0001 | 2022-08-01 00:00:00  
orange | 2022-08-21 00:00:00 | 0001 | 2022-08-01 00:00:00  
grape | 2022-08-30 00:00:00 | 0001 | 2022-08-01 00:00:00  
apple2 | 2022-09-11 00:00:00 | 0001 | 2022-09-01 00:00:00  
orange | 2022-09-21 00:00:00 | 0001 | 2022-09-01 00:00:00  
grape | 2022-09-30 00:00:00 | 0001 | 2022-09-01 00:00:00  
apple2 | 2022-10-11 00:00:00 | 0001 | 2022-09-01 00:00:00  
ice | 2022-10-15 00:00:00 | 0001 | 2022-10-01 00:00:00  
orange | 2022-10-21 00:00:00 | 0001 | 2022-09-01 00:00:00  
grape | 2022-10-30 00:00:00 | 0001 | 2022-09-01 00:00:00  
  
* * *

[View on DB Fiddle](https://www.db-fiddle.com/f/oAqMHAgF3LdPdrecjEeMYb/0)

