【SQL】テーブルの縦横を切り替えてサブクエリとして使用する実例

仕事で使用したので備忘録として記載しておきます。

今回は例としてイベントチケット管理システムを開発するという前提で考えていきたいと思います。
イベントは複数開催されていて、それぞれのイベントは子供料金・大人料金が別々のチケットで管理されています。ただ、イベントによっては大人しか参加できないという規制があるとします。イベントAは子供と大人のチケットが存在し、イベントBでは大人のみという形になります。

そして、最終的に実行したい処理は「子供と大人の両方が参加可能なイベントを取得する」とします。

テーブルの作成

以下のように4つのテーブルとしました。
・イベントテーブル
・チケットテーブル
・料金テーブル
・料金項目テーブル

SQL

-- イベントテーブル --
CREATE TABLE events (
	id MEDIUMINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
	name VARCHAR(255) NOT NULL,
	created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
	updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- チケットテーブル --
CREATE TABLE tickets (
	id MEDIUMINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
	event_id MEDIUMINT NOT NULL,
	event_start DATETIME NOT NULL,
	event_end DATETIME NOT NULL,
	created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
	updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- 料金テーブル --
CREATE TABLE prices (
	id MEDIUMINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
	ticket_id MEDIUMINT NOT NULL,
	item_id MEDIUMINT NOT NULL,
	price INT NOT NULL,
	created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
	updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- 料金項目テーブル --
CREATE TABLE items (
	id MEDIUMINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
	item_name VARCHAR(255) NOT NULL,
	created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
	updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

データはそれぞれ以下になります。

SQL

-- イベントテーブル --
INSERT INTO events(name) VALUES('サンプルイベント01')
,('サンプルイベント02')
,('サンプルイベント03');

-- チケットテーブル --
INSERT INTO tickets(event_id, event_start, event_end) VALUES(1, '2021-02-01 10:00:00', '2021-02-01 20:00:00')
,(2, '2021-02-01 10:00:00', '2021-02-01 20:00:00')
,(3, '2021-02-01 10:00:00', '2021-02-01 20:00:00');

-- 料金テーブル --
INSERT INTO prices(ticket_id, item_id, price) VALUES(1, 1, '1000')
,(1, 2, '600')
,(2, 1, '1000')
,(2, 2, '400')
,(3, 1, '1000');

-- 料金項目テーブルル --
INSERT INTO items(item_name) VALUES('大人')
,('子ども');

データを取得する

そして、「子供と大人の両方が参加可能なイベントを取得する」ためのSQLが以下です。

SQL

SELECT
	e.id,
	e.name
FROM events AS e
JOIN tickets AS t ON t.event_id = e.id
WHERE t.id IN (
	SELECT
		price.ticket_id
	FROM (
		SELECT
			tickets.id AS ticket_id,
			MAX(CASE item_id WHEN 1 THEN 1 END) AS adult,
			MAX(CASE item_id WHEN 2 THEN 1 END) AS child
		FROM tickets
		JOIN prices AS p ON p.ticket_id = tickets.id
		GROUP BY tickets.id
	) AS price
	WHERE price.adult IS NOT NULL
	AND price.child IS NOT NULL
)

サブクエリの中のサブクエリだけを実行すると以下のようなテーブルが取得できます。

ticket_id	adult	child
1	1	1
2	1	1
3	1	null

料金テーブルには大人料金・子供料金でそれぞれ1レコードづつ入っているとして、大人しか参加できないイベントは大人料金のチケットしか存在しません。
そのため、サブクエリの中のサブクエリのSQL（テーブルの縦横を切り替えたもの）を叩くことでnullになるところが料金レコードがないものとなります。今回のデータではチケットIDが3のものは子供チケットはないことになります。

そのあとはサブクエリとしてadultとchildがnullではないチケットIDを取り出して、上層のwhere inの条件として渡してあげれば「子供と大人の両方が参加可能なイベントを取得する」ことができました。