TL;DR
WARNING: 下記SQLコードはDB FiddleのSQLite v3.39で検証済みです、MySQL、Postgres、SQL Serverなどで運用する際は適宜に書き換えてください
一時テーブルを作ってJOINする
CREATE TEMPORARY TABLE 平日 (
ID INT, 曜日 VARCHAR(50)
);
INSERT INTO 平日(ID, 曜日) VALUES (1, '月'), (2, '火'), (3, '水'), (4, '木'), (5, '金');
定数表で仮想テーブルを作ってJOINする
再利用しやすいようにWITH句を使っていますが、そのままSQL文にJOINしても構いません。
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 句を活用すること。
但し、VALUES句はそれぞれの方言では書き方が非常に異なるため、特にSQL Server、UNION ALL句の方は互換性が良いです。
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がなければ、一時テーブル作ると良いです。
早速「時間割」の一時テーブルを作成します。
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を行います。
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行を全部書くのはナンセンスなので、一部に絞ります。
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パターンで作成できるので、一種類ずつ作ってみます。
-- 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実行する際は一緒に実行しないとエラーが発生します。
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 | ○ |