MySQLで再帰的にコード変換テーブルを参照し、コード値の書き換えを行いたい

Question

MySQL8.0で前年度の売上を取得するSQLを作成しているのですが、
販売終了した商品を商品コード変換テーブルを使って現在販売中の商品の売上として計上する必要があり、
シンプルな商品コード変換テーブルと売上テーブルの結合方法を模索しています。
参照テーブルは2つです。
①商品コード変換テーブル：
いつから変換するか対象の年月（半年毎）、変換前コード、変換後のコードを持っています。
②売上テーブル：
商品コードと売上金額を持っています。

変換が発生するパターンは次の3パターンです。
①2回変換が発生
2期前（商品C）→ 1期前（商品D）→ 当期（商品A）
②1回変換が発生
2期前（商品B）→ 1期前（商品A）※当期も商品Aで計上
1期前（商品E）→ 当期（商品A）
③変換なし

テーブルの値は以下のようになります。
■商品コード変換テーブル

変換年月	変換前商品コード	変換後商品コード
202001	商品C	商品D
202001	商品B	商品A
202007	商品D	商品A
202007	商品E	商品A

■売上テーブル

商品コード	売上金額
商品A	100
商品B	200
商品C	300
商品D	400
商品E	500

商品Aの前年度の売上は商品B～Eを足した金額である1500になるのですが、これを実現するSQLをご教示頂けないでしょうか。
CASE式を使って①2回変換、②1回変換、③変換なしの3パターンをチェックしていく方法を考えたのですが、
冗長なSQLになってしまい、再帰的に商品コード変換テーブルを参照してもっとシンプルなSQLにできないものかと考えています。

Answer 1

SELECT COALESCE(H2.変換後商品コード, H1.変換後商品コード, 売上テーブル.商品コード) 商品コード
, SUM(売上テーブル.売上金額) 合計金額
FROM 売上テーブル
LEFT JOIN 商品コード変換テーブル AS H1 ON 売上テーブル.商品コード = H1.変換前商品コード
LEFT JOIN 商品コード変換テーブル AS H2 ON H1.変換後商品コード = H2.変換前商品コード
GROUP BY COALESCE(H2.変換後商品コード, H1.変換後商品コード, 売上テーブル.商品コード)
```みたいな感じ。
MySQL8.0 の文法に合ってなかったらゴメン。

Comments

[kmrn]

ありがとうございます！試してみます。