SQLのLEFT JOINの条件の書き方

Question

以下のテーブルAとテーブルBからテーブルCを抽出したいのですが、JOIN の条件がわからず、うまく抽出することができないため教えていただきたいです。

【テーブルA】
NO, 売上日
1, 2023-04-16
2, 2023-05-16
3, 2023-06-16

【テーブルB】
適用日, 消費税
2023-04-01, 0.01
2023-04-15, 0.02
2023-05-01, 0.03
2023-05-15, 0.04
2023-06-01, 0.05
2023-06-15, 0.06

【求めたいテーブル（テーブルAとテーブルBをJOINした結果）】
NO, 売上日, 消費税
1, 2023-04-16, 0.02
2, 2023-05-16, 0.04
3, 2023-06-16, 0.06

【現在のSQL】
SELECT
テーブルA.NO
,テーブルA.売上日
,テーブルB.消費税
FROM テーブルA
LEFT JOIN テーブルB
ON テーブルB.適用日 <= テーブルA.売上日

【実行結果】
NO, 売上日, 消費税
1, 2023-04-16, 0.01
1, 2023-04-16, 0.02
2, 2023-05-16, 0.01
2, 2023-05-16, 0.02
2, 2023-05-16, 0.03
2, 2023-05-16, 0.04
3, 2023-06-16, 0.01
3, 2023-06-16, 0.02
3, 2023-06-16, 0.03
3, 2023-06-16, 0.04
3, 2023-06-16, 0.05
3, 2023-06-16, 0.06

テーブルB.適用日 <= テーブルA.売上日、の条件の中で最も新しい適用日の消費税のみをJOINしたいですが、書き方がわかりません。

Comments

[退会済みユーザー]

DB サーバーはタグの SQL Server なのですか？　あなたの言うJOIN の意味は SQL 文で言う JOIN とは違うようですが、具体的にどういうことか説明できますか？

Answer 1

LEAD関数を使って有効な日付の範囲を求めたものと結合します。

select 
        ta.*, tb.消費税
from  tbl_a as ta
      left join (
        select *, lead(適用日, 1, cast('9999/12/31' as date)) over(order by 適用日) as 次回適用日
      ) as tb
      on  ta.売上日 >= tb.適用日 and ta,売上日 < tb,次回適用日

※手打ちで検証していません。悪しからず。

Comments

[sasaki0628]

lead関数は知りませんでした。シンプルでわかりやすいですね。ありがとうございます。

Answer 2

もし消費税マスタの形をいじれるならば、適用開始日・終了日を持って left join 一発でやる方法もあります。

適用開始日, 適用終了日(NULLは終了日未定), 消費税
2023-04-01, 2023-04-14, 0.01
2023-04-15, 2023-04-30, 0.02
2023-05-01, NULL, 0.03

Comments

[sasaki0628]

なるほど、こちらが範囲指定ならば簡単に書けるんですね。（ただし今回は修正することができません…。）ありがとうございます。

Answer 3

とりあえずmysqlで書いておきます、ご自身のSQLへの適用は調整ください

create table tbl_a(no int primary key,d date);
insert into tbl_a values
(1,'2023-04-16'),
(2,'2023-05-16'),
(3,'2023-06-16');

create table tbl_b(d date,tax double);
insert into tbl_b values
('2023-04-01',0.01),
('2023-04-15',0.02),
('2023-05-01',0.03),
('2023-05-15',0.04),
('2023-06-01',0.05),
('2023-06-15',0.06);

select t1.no,t1.d,t2.tax from tbl_a as t1
left join tbl_b as t2
on t1.d>t2.d
and not exists(select 1 from tbl_b where d<t1.d and d>t2.d)

Comments

[sasaki0628]

回答ありがとうございます。EXISTSは知りませんでした。

テーブルBが

適用日, 消費税
2023-04-01, 0.01
2023-04-15, 0.02
2023-04-16, 0.07
2023-05-01, 0.03
2023-05-15, 0.04
2023-06-01, 0.05
2023-06-15, 0.06

だったときに、売上日が 2023-04-16 のときに 適用日が 2023-04-15 のものが結合されてしまいます。（2023-04-16 が結合されたい。）
教えていただいたSQLを参考にして

SELECT tbl_a.NO, tbl_a.売上日, tbl_b.消費税 FROM tbl_a
LEFT JOIN tbl_b AS tbl_b_1
on tbl_a.売上日 >= tbl_b.適用日
AND NOT EXISTS(SELECT 1 FROM tbl_b AS tbl_b_2
WHERE tbl_b_2.適用日 < tbl_a.売上日 AND tbl_b_2.適用日 > tbl_b_1.適用日)

としたところ、今度は 2023-04-16 と 2023-04-15 の二つが結合されました。
（tbl_a.売上日 >= tbl_b.適用日 としているため。）

そこで、サブクエリで適用日が最大のものに絞った後に結合をしました。

SELECT tbl_a_1.NO, tbl_a_1.売上日, tbl_b_1.消費税
FROM tbl_a AS tbl_a_1
LEFT JOIN(
SELECT tbl_a_2.売上日, tbl_b_2.消費税
FROM tbl_b AS tbl_b_2
LEFT JOIN(
SELECT tbl_a_3.売上日, MAX(tbl_b_3.適用日) AS 適用日
FROM tbl_a AS tbl_a_3
LEFT JOIN tbl_b AS tbl_b_3
ON tbl_a_3.売上日 >= tbl_b_3.適用日
AND NOT EXISTS(SELECT 1 FROM tbl_b AS tbl_b_4
WHERE
　　tbl_b_4.適用日 < tbl_a_3.売上日
AND tbl_b_4.適用日 > tbl_b_3.適用日 )
GROUP BY tbl_a_3.売上日
) AS tbl_a_2
ON tbl_b_2.適用日 = tbl_a_2.適用日
) AS tbl_b_1
ON tbl_a_1.売上日 = tbl_b_1.売上日

サブクエリを3回も使い、良い書き方ではないように思います。もし他にシンプルな書き方があれば教えていただけないでしょうか。

[yambejp]

同じ日がふくまれるならこう
select t1.no,t1.d,t2.tax,t2.d from tbl_a as t1
left join tbl_b as t2
on t1.d>=t2.d
and not exists(select 1 from tbl_b where d<=t1.d and d>t2.d)

[sasaki0628]

ありがとうございます。
実は、WHERE句のところの意味がよくわかっていません。
売上日が 2023-04-16 のとき、
d <= t1.d
においては、2023-04-16 以下の日付として
2023-04-01, 2023-04-15, 2023-04-16
の3つに絞られていると思いますが、
d>t2.d
においては、何をしているのでしょうか？
2023-04-01, 2023-04-15, 2023-04-16 > 2023-04-01, 2023-04-15, 2023-04-16
のとき、2023-04-01, 2023-04-15 が残るのでしょうか？

[sasaki0628]

EXISTS句を調べ学習し、理解しました。ありがとうございました。