SQLでテーブルに定数(配列)をJOINする

# TL;DR
__*<font color="orange">WARNING: 下記SQLコードは[DB Fiddle](https://www.db-fiddle.com/)の`SQLite v3.39`で検証済みです、MySQL、Postgres、SQL Serverなどで運用する際は適宜に書き換えてください</font>*__

## 一時テーブルを作ってJOINする

```sql
CREATE TEMPORARY TABLE 平日 (
  ID INT, 曜日 VARCHAR(50)
);

INSERT INTO 平日(ID, 曜日) VALUES (1, '月'), (2, '火'), (3, '水'), (4, '木'), (5, '金');
```

## 定数表で仮想テーブルを作ってJOINする
再利用しやすいように`WITH`句を使っていますが、そのままSQL文にJOINしても構いません。

```sql
WITH 平日 AS (
  SELECT 1 AS ID, '月' AS 曜日
  UNION ALL
  SELECT 2 AS ID, '火' AS 曜日
  UNION ALL
  SELECT 3 AS ID, '水' AS 曜日
  UNION ALL
  SELECT 4 AS ID, '木' AS 曜日
  UNION ALL
  SELECT 5 AS ID, '金' AS 曜日
)
```

または `VALUES` 句を活用すること。
*<font color='gray'>但し、`VALUES`句はそれぞれの方言では書き方が非常に異なるため、~~特にSQL Server~~、`UNION ALL`句の方は互換性が良いです。*

```sql
WITH 平日(ID, 曜日) AS (
  VALUES (1, '月'), (2, '火'), (3, '水'), (4, '木'), (5, '金')
)
```

# はじめに
とある学校から、一週間限定の夏期講習のために、講師のスケジュール一覧表を出したいと要望が来ました。
しかし既存のデータベース上に「講師名簿」しか存在しておらず、テーブルやカラムの追加はもちろん、アプリケーションサーバーの編集も不可となっています。

# データ構成
早速、担当スタッフさんからデータを頂きました。

まずはDB上の　**「講師名簿」**　です。

| ID  | 先生  | 担当科目 |
| --- | --- | ---- |
| 1   | 愛宕  | 航海   |
| 2   | 高雄  | 機関   |
| 3   | 摩耶  | 英語   |
| 4   | 妙高  | 砲雷   |
| 5   | 那智  | 通信   |

そして夏期講習の **「時間割」** ももらいましたが、一週間しかない臨時講習のためか、DBに登録していません。登録する場所もありません。


| 時限  | 時間          | 月   | 火   | 水   | 木   | 金   |
| --- | ----------- | --- | --- | --- | --- | --- |
| 1   | 10:00~10:50 | 航海  | 機関  | 英語  | 砲雷  | 通信  |
| 2   | 11:00~11:50 | 機関  | 英語  | 砲雷  | 通信  | 航海  |
| 3   | 13:00~13:50 | 英語  | 砲雷  | 通信  | 航海  | 機関  |
| 4   | 14:00~14:50 | 砲雷  | 通信  | 航海  | 機関  | 英語  |
| 5   | 15:00~15:50 | 通信  | 航海  | 機関  | 英語  | 砲雷  |

# 方法
もちろん、このような状況はRubyやPythonなどでロジックを書いて処理した方が圧倒的に簡単ですし、Excelなど表計算ソフトを慣れていた方にとっても簡単かと思います。

しかしSQL文だけでもできなくもないです。

## その1: 一時Tableを作る
Tableがなければ、一時テーブル作ると良いです。

早速「時間割」の一時テーブルを作成します。

```sql
CREATE TEMPORARY TABLE 時間割 (
  時限 INT,
  時間 VARCHAR(50),
  月 VARCHAR(50),
  火 VARCHAR(50),
  水 VARCHAR(50),
  木 VARCHAR(50),
  金 VARCHAR(50)
);

INSERT INTO 時間割 VALUES (1, '10:00~10:50', '航海', '機関', '英語', '砲雷', '通信');
INSERT INTO 時間割 VALUES (2, '11:00~11:50', '機関', '英語', '砲雷', '通信', '航海');
INSERT INTO 時間割 VALUES (3, '13:00~13:50', '英語', '砲雷', '通信', '航海', '機関');
INSERT INTO 時間割 VALUES (4, '14:00~14:50', '砲雷', '通信', '航海', '機関', '英語');
INSERT INTO 時間割 VALUES (5, '15:00~15:50', '通信', '航海', '機関', '英語', '砲雷');
```

### TIPS: 条件なしの`CROSS JOIN`を使って行数を増幅

データ構成を見る限り、「時間割」と「曜日」の二次元配列を「曜日+時間割」をKeyとし、「科目」をValueとしたTableにしないと、教師データとまともにJOINできません。

そこに、「平日」というTableを中間テーブルとして作成し、条件なしの`CROSS JOIN`を行います。

```sql
CREATE TEMPORARY TABLE 平日 (
  ID INT,
  曜日 VARCHAR(50)
);

INSERT INTO 平日 VALUES (1, '月'), (2, '火'), (3, '水'), (4, '木'), (5, '金');
```

こうやって「平日」テーブルの行数分、データが増幅されます。
同様に、「時間割」も`CROSS JOIN`すれば、最終的に下記の数のレコードになります。

```
講師人数 x 日数 x 時限数
```

つまり `5 x 5 x 5 = 125` 行です。

### 結果

流石に125行を全部書くのはナンセンスなので、一部に絞ります。

```sql
SELECT DISTINCT
  講師名簿.ID AS 講師ID,
  先生,
  担当科目,
  曜日,
  時限,
  時間,
  CASE
    WHEN 曜日 = '月' THEN (CASE WHEN 時間割.月 = 講師名簿.担当科目 THEN '○' ELSE '' END)
    WHEN 曜日 = '火' THEN (CASE WHEN 時間割.火 = 講師名簿.担当科目 THEN '○' ELSE '' END)
    WHEN 曜日 = '水' THEN (CASE WHEN 時間割.水 = 講師名簿.担当科目 THEN '○' ELSE '' END)
    WHEN 曜日 = '木' THEN (CASE WHEN 時間割.木 = 講師名簿.担当科目 THEN '○' ELSE '' END)
    WHEN 曜日 = '金' THEN (CASE WHEN 時間割.金 = 講師名簿.担当科目 THEN '○' ELSE '' END)
    ELSE ''
  END AS スケジュール
FROM 講師名簿
  CROSS JOIN 平日
  CROSS JOIN 時間割
WHERE 先生 = '高雄'
  AND スケジュール = '○'
ORDER BY 講師ID, 平日.ID, 時間割.時限
```

| 講師ID | 先生  | 担当科目 | 曜日  | 時限  | 時間          | スケジュール |
| ---- | --- | ---- | --- | --- | ----------- | ------ |
| 2    | 高雄  | 機関   | 月   | 2   | 11:00~11:50 | ○      |
| 2    | 高雄  | 機関   | 火   | 1   | 10:00~10:50 | ○      |
| 2    | 高雄  | 機関   | 水   | 5   | 15:00~15:50 | ○      |
| 2    | 高雄  | 機関   | 木   | 4   | 14:00~14:50 | ○      |
| 2    | 高雄  | 機関   | 金   | 3   | 13:00~13:50 | ○      |



## その2: Query文に定数を仕込む
もし`CREATE`句そのものが封印された場合でも、定数表を作ることで解決できます。

参照しやすいように、`WITH`文を使ってサブクエリーを作成していますが、直接サブクエリーを書いても構いません。

また、定数表は`UNION ALL`句と`VALUES`句の2パターンで作成できるので、一種類ずつ作ってみます。

```sql
-- UNION ALL
WITH 時間割 AS (
  SELECT 1 AS 時限, '10:00~10:50' AS 時間, '航海' AS 月, '機関' AS 火, '英語' AS 水, '砲雷' AS 木, '通信' AS 金
  UNION ALL
  SELECT 2 AS 時限, '11:00~11:50' AS 時間, '機関' AS 月, '英語' AS 火, '砲雷' AS 水, '通信' AS 木, '航海' AS 金
  UNION ALL
  SELECT 3 AS 時限, '13:00~13:50' AS 時間, '英語' AS 月, '砲雷' AS 火, '通信' AS 水, '航海' AS 木, '機関' AS 金
  UNION ALL
  SELECT 4 AS 時限, '14:00~14:50' AS 時間, '砲雷' AS 月, '通信' AS 火, '航海' AS 水, '機関' AS 木, '英語' AS 金
  UNION ALL
  SELECT 5 AS 時限, '15:00~15:50' AS 時間, '通信' AS 月, '航海' AS 火, '機関' AS 水, '英語' AS 木, '砲雷' AS 金
),
-- VALUES
平日(ID, 曜日) AS (
  VALUES (1, '月'), (2, '火'), (3, '水'), (4, '木'), (5, '金')
)
```

### 結果
編集の都合上で`SELECT`文を上記`WITH`文と分けていましたが、実際SQL実行する際は一緒に実行しないとエラーが発生します。

```sql
SELECT DISTINCT
  講師名簿.ID AS 講師ID,
  先生,
  担当科目,
  曜日,
  時限,
  時間,
  CASE
    WHEN 曜日 = '月' THEN (CASE WHEN 時間割.月 = 講師名簿.担当科目 THEN '○' ELSE '' END)
    WHEN 曜日 = '火' THEN (CASE WHEN 時間割.火 = 講師名簿.担当科目 THEN '○' ELSE '' END)
    WHEN 曜日 = '水' THEN (CASE WHEN 時間割.水 = 講師名簿.担当科目 THEN '○' ELSE '' END)
    WHEN 曜日 = '木' THEN (CASE WHEN 時間割.木 = 講師名簿.担当科目 THEN '○' ELSE '' END)
    WHEN 曜日 = '金' THEN (CASE WHEN 時間割.金 = 講師名簿.担当科目 THEN '○' ELSE '' END)
    ELSE ''
  END AS スケジュール
FROM 講師名簿
  CROSS JOIN 平日
  CROSS JOIN 時間割
WHERE 先生 = '摩耶'
  AND スケジュール = '○'
ORDER BY 講師ID, 平日.ID, 時間割.時限
```

| 講師ID | 先生  | 担当科目 | 曜日  | 時限  | 時間          | スケジュール |
| ---- | --- | ---- | --- | --- | ----------- | ------ |
| 3    | 摩耶  | 英語   | 月   | 3   | 13:00~13:50 | ○      |
| 3    | 摩耶  | 英語   | 火   | 2   | 11:00~11:50 | ○      |
| 3    | 摩耶  | 英語   | 水   | 1   | 10:00~10:50 | ○      |
| 3    | 摩耶  | 英語   | 木   | 5   | 15:00~15:50 | ○      |
| 3    | 摩耶  | 英語   | 金   | 4   | 14:00~14:50 | ○      |


# 参考
- [結合条件が不要な場合のテーブル結合（Cross Join）](https://manymanytips.hatenadiary.org/entry/20110324/p1)
- [SELECT文中で定数テーブルを作る方法がほしい](https://qiita.com/Hiraku/items/c6e745255d5de46e2756)
- [VALUES構文](https://docs.snowflake.com/ja/sql-reference/constructs/values)