【電脳少女プログラミング2088-壊レタ君を再構築-】A:巨大コーポの最上階をCTE？？を使用して解いてみた

こんにちは。

今回は電脳少女プログラミング2088-壊レタ君を再構築-のA：巨大コーポの最上階に取り組みました。
そのなかで、レイミから「 **CTE** を使用して改善してください。」とのフィードバックがありました。
実際にCTEを使用してみました。その内容を投稿させたいただきます。

問題は以下のサイト

https://ppg.paiza.jp/denno2088?from=entertainment

# 問題

>あなたは軍の機密情報へアクセスできる端末を見つけました。そこから重要なデータだけを得ようとし>ています。
>log (ログ), memory (記憶), battle (戦闘), category (カテゴリ), person (人物) のテーブルが以下の図のように与えられます
>person (人物) のうち重要度が 5 の人物の person.deleted_at と同じ battle (戦闘) の battle.created_at (戦闘.作成日) から memory (記憶) と battle (戦闘) と person (人物) の memory.id (記憶.ID), memory.talk (記憶.会話内容), person.name (人物.人物名), battle.created_at (戦闘.作成日) を出力してください。

![スクリーンショット 2025-01-28 21.24.40.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3992689/44c70e82-67eb-5851-814e-f84222a146f5.png)

# 考え方と解答
個人的な考えとして結合が多そうなテーブルを軸としたいなぁと。。。
（この考えが正しいとは限らない）
そこでlogテーブルを軸として考えました。
イメージとして
+ logテーブルとmemoryテーブルを記憶のIDとIDで結合
+ logテーブルにbattleテーブルとpersonテーブルを人物のIDとIDで結合したものを人物のIDで結合
+ categoryテーブルは必要なさそうなので結合しない

この考えと条件踏まえかつ条件に合うように作成したSQLが以下の通り
```SQL:MySQL
SELECT memory.id,memory.talk,data.name,data.created_at
FROM log
 INNER JOIN memory
   ON  log.memory_id = memory.id
 INNER JOIN (SELECT battle.person_id as person_id, person.name as name,
                    battle.created_at as created_at
            FROM battle 
             INNER JOIN person
               ON battle.person_id = person.id
            WHERE person.importance >= 5
              AND battle.created_at = person.deleted_at)data
   ON log.person_id = data.person_id 
```
このソースでも良いと思うが、レイミのフィードバックは次のようでした。
![スクリーンショット 2025-01-28 21.39.51.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3992689/c496a120-e50a-35ee-1d64-bda99bcb776c.png)

タイトルでも書いていますが、CTE？？？？？？？？？？？？？？？？？？？
仕事でSQLを使用していますが、初めて聞いた言葉でした。

# CTEとは
謎すぎるので検索してみました。
>メインのSQLの問い合わせを実行するために補助的に使う、一時テーブルを（WITH句を使って）定義するものです。
>…というと、派生テーブル（FROM句のサブクエリ）と何が違うの？となりますが、（非再帰のCTEはWITH句を使うか使わないか等の書式の違いを除いて、派生テーブルとほぼ同じようなものですので）実質的な違いは「再帰的に書ける」ところです。
>参照先　https://qiita.com/hmatsu47/items/01211556089b19913d05

つまりサブクエリをメインとSQL文の外に書き共通化しようとのことです。英語的にも共通テーブルということのようです。何度も同じ結合したテーブルを使用した時にとても便利だと思いました！
>CTE : Common Table Expressions
>参照先　https://qiita.com/hmatsu47/items/01211556089b19913d05



使い方は以下の通り、WITH句を使用します。
>```SQL:MySQL
>WITH
> cte1 AS (SELECT a, b FROM table1),
> cte2 AS (SELECT c, d FROM table2)
>SELECT b, d FROM cte1 JOIN cte2
>WHERE cte1.a = cte2.c;
>```
>参照先　https://dev.mysql.com/doc/refman/8.0/ja/with.html

# CTEを使用して解答を作成してみた
CTEを使用して書いたSQLは以下の通りです。
```SQL:MySQL
WITH
btl AS 
(SELECT battle.person_id as person_id, 
        person.name as name,
        battle.created_at as created_at
 FROM battle 
    INNER JOIN person
            ON battle.person_id = person.id
 WHERE person.importance = 5
   AND battle.created_at = person.deleted_at),
memo AS 
(SELECT log.person_id as person_id,
         memory.id,memory.talk as talk
 FROM log
   INNER JOIN memory
           ON  log.memory_id = memory.id)
  
SELECT memo.id,
       memo.talk,
       btl.name,
       btl.created_at
FROM memo 
  INNER JOIN btl
    ON memo.person_id = btl.person_id
```

メインのSQLはとてもシンプルとなりました。
共通テーブルは以下の２つ
btlテーブル：battleテーブルとpersonテーブルを人物IDとIDで結合し、問題文の条件に合うようにWHERE句に記載したテーブル
memoテーブル：logテーブルとmemoryテーブルを記憶のIDとIDで結合したテーブいる

２つの共通テーブルを人物IDで結合しました。

# CTEを使ってみた感想
今回は初めてCTEを使用したSQLを作成してみました。正直なところ今回に関してはあんまりメリットがないというか。CTEを使用しない方がシンプルと思われる方も多いかもしれません。

ただ、知識として蓄え使えるときに活用したいと思いました。
SQLも奥が深いですね！！

# 電脳少女プログラミング2088-壊レタ君を再構築-のSQLの問題について
今回、A~DランクのSQLの問題がありすべて挑戦しました。高ランクでも基礎を押さえていれば解ける問題となっております。SQLに苦手意識がある人でもゲーム感覚でできるのでオススメです。
是非、チャレンジしてスキルアップしてくださいね！