Mysql
根據使用子查詢預訂的數量計算門票數量
我正在設計一個保存票務資訊的數據庫。我用來跟踪票務的 3 個表是
tbl_ticket_type一個事件可以有的事件類型
tbl_ticket_count跟踪應為某個活動出售多少特定類型的門票
tbl_tickets跟踪使用者購買的實際門票。我正在嘗試設計一個查詢，該查詢將計算特定事件的剩餘門票類型數量，並以剩餘類型和數量的組返回結果。我嘗試了以下 SQL 語句但無濟於事，如何修復 SQL 以返回剩餘票的類型和數量？附有 DB 插圖
SELECT tt.ticket_type,COUNT(tt.ticket_type) FROM tbl_tickets t INNER JOIN tbl_ticket_count tc ON t.ticket_count_id=tc.ticket_count_id INNER JOIN tbl_ticket_type tt ON tc.ticket_type_id = tt.ticket_type_id WHERE ticket_count_id IN (SELECT tc.ticket_count_id FROM tbl_ticket_count WHERE event_id=15) GROUP BY tt.ticket_type重新創建三個表的腳本：
tbl_tickets
CREATE TABLE `tbl_tickets` ( `ticket_id` int(11) NOT NULL , `ticket_count_id` int(11) DEFAULT '0' , `userid` int(11) DEFAULT '0' , `provider_id` int(11) DEFAULT '1', `pay_status` enum('reserved','paid','pending','') DEFAULT 'reserved' , `reference` varchar(50) DEFAULT NULL COMMENT 'if e-payment, what the reference for the payment', `event_status` enum('pending','checked','','') NOT NULL DEFAULT 'pending' , `date_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ) ENGINE=InnoDB DEFAULT CHARSET=latin1 ; ALTER TABLE `tbl_tickets` ADD PRIMARY KEY (`ticket_id`), ADD KEY `ticket_count_id` (`ticket_count_id`), ADD KEY `user_id` (`userid`), ADD KEY `provider_id` (`provider_id`); ALTER TABLE `tbl_tickets` ADD CONSTRAINT `tbl_tickets_ibfk_1` FOREIGN KEY (`provider_id`) REFERENCES `tbl_provider` (`provider_id`), ADD CONSTRAINT `tbl_tickets_ibfk_2` FOREIGN KEY (`ticket_count_id`) REFERENCES `tbl_ticket_count` (`ticket_count_id`) ON DELETE CASCADE ON UPDATE CASCADE; INSERT INTO `tbl_tickets` (`ticket_id`, `ticket_count_id`, `userid`, `provider_id`, `pay_status`, `reference`, `event_status`, `date_time`) VALUES (3, 1, 2, 1, 'reserved', NULL, 'pending', '2017-11-04 15:14:15'), (4, 1, 6, 1, 'pending', NULL, 'pending', '2017-11-04 15:14:15'), (5, 10, 6, 1, 'reserved', NULL, 'pending', '2017-11-04 16:32:35'), (6, 10, 2, 1, 'reserved', NULL, 'pending', '2017-11-04 16:32:35');tbl_ticket_count
CREATE TABLE `tbl_ticket_count` ( `ticket_count_id` int(11) NOT NULL , `ticket_type_id` int(11) DEFAULT '0' , `event_id` int(11) DEFAULT '0' , `quantity` int(11) DEFAULT '0' , `price` decimal(10,0) DEFAULT '0' , `date_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ) ENGINE=InnoDB DEFAULT CHARSET=latin1; ALTER TABLE `tbl_ticket_count` ADD PRIMARY KEY (`ticket_count_id`), ADD KEY `ticket_type_id` (`ticket_type_id`), ADD KEY `event_id` (`event_id`); ALTER TABLE `tbl_ticket_count` ADD CONSTRAINT `tbl_ticket_count_ibfk_1` FOREIGN KEY (`ticket_type_id`) REFERENCES `tbl_ticket_type` (`ticket_type_id`) ON DELETE CASCADE ON UPDATE CASCADE, ADD CONSTRAINT `tbl_ticket_count_ibfk_2` FOREIGN KEY (`event_id`) REFERENCES `tbl_events_new` (`event_id`) ON DELETE CASCADE ON UPDATE CASCADE; INSERT INTO `tbl_ticket_count` (`ticket_count_id`, `ticket_type_id`, `event_id`, `quantity`, `price`, `date_time`) VALUES (1, 1, 3, 10, 15000, '2017-11-04 14:45:02'), (2, 2, 3, 10, 25000, '2017-11-04 14:45:02'), (3, 1, 9, 10, 15000, '2017-11-04 14:45:39'), (4, 2, 9, 10, 25000, '2017-11-04 14:45:51'), (5, 1, 4, 100, 2000, '2017-11-04 14:47:13'), (6, 1, 5, 50, 4000, '2017-11-04 14:48:17'), (7, 5, 10, 15, 100000, '2017-11-04 14:48:17'), (8, 1, 8, 50, 0, '2017-11-04 14:48:48'), (9, 7, 15, 10, 3000, '2017-11-04 16:31:47'), (10, 1, 15, 20, 7500, '2017-11-04 16:31:47');tbl_ticket_type
CREATE TABLE `tbl_ticket_type` ( `ticket_type_id` int(11) NOT NULL COMMENT 'stores unique identification for the ticket type', `ticket_type` varchar(50) DEFAULT NULL COMMENT 'type of ticket ', `description` varchar(500) DEFAULT NULL COMMENT 'description of that ticket type_category', `date_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'datetime the type_category was added' ) ENGINE=InnoDB DEFAULT CHARSET=latin1; ALTER TABLE `tbl_ticket_type` ADD PRIMARY KEY (`ticket_type_id`); INSERT INTO `tbl_ticket_type` (`ticket_type_id`, `ticket_type`, `description`, `date_time`) VALUES (1, 'Standard', 'These are standard entry tickets that will permit one person into an event venue', '2017-11-04 14:42:41'), (2, 'VIP', 'These are VIP tickets with VIP access to an event venue', '2017-11-04 14:42:41'), (3, 'VIPP', 'VIPP tickets to access the VVIP sections of an event venue', '2017-11-04 14:43:32'), (4, 'Family', 'Tickets to permit a family of a pre determined number by the organisers', '2017-11-04 14:43:32'), (5, 'Team', 'Team ticket to some events e.g. sporting tournaments', '2017-11-04 14:44:04'), (6, 'Couples', 'Admits two (2) people into an event venue', '2017-11-04 15:06:00'), (7, 'Children', 'Tickets aimed at children', '2017-11-04 16:31:00');
也許以下查詢將幫助您找到正確的答案（MySQL 5.7）。假設：我們需要售出和未售出的門票數量。tbl_tickets 中的每一行代表一張已售票。使用 LEFT JOIN，我們包含“未售出”的門票：
select tc.ticket_count_id TC_ID , t.ticket_count_id T_TC_ID , tc.ticket_type_id CATEGORY , tc.event_id EVENT , tc.quantity , count(t.ticket_count_id) SOLD , tc.quantity - count(t.ticket_count_id) TICKETS_LEFT from tbl_ticket_count tc left join tbl_tickets t on tc.ticket_count_id = t.ticket_count_id group by tc.ticket_count_id having tc.event_id = 15 ; -- result +-------+---------+----------+-------+----------+------+--------------+ | TC_ID | T_TC_ID | CATEGORY | EVENT | quantity | SOLD | TICKETS_LEFT | +-------+---------+----------+-------+----------+------+--------------+ | 9 | NULL | 7 | 15 | 10 | 0 | 10 | | 10 | 10 | 1 | 15 | 20 | 2 | 18 | +-------+---------+----------+-------+----------+------+--------------+ 2 rows in set (0.00 sec)如果這對您來說正確，請加入第三個表，並調整 SELECT 子句的列。
select tc.event_id EVENT , tc.quantity - count(t.ticket_count_id) TICKETS_LEFT , tt.ticket_type from tbl_ticket_count tc left join tbl_tickets t on tc.ticket_count_id = t.ticket_count_id join tbl_ticket_type tt on tc.ticket_type_id = tt.ticket_type_id group by tc.ticket_count_id having tc.event_id = 15 ; -- result +-------+--------------+-------------+ | EVENT | TICKETS_LEFT | ticket_type | +-------+--------------+-------------+ | 15 | 10 | Children | | 15 | 18 | Standard | +-------+--------------+-------------+ 2 rows in set (0.00 sec)*注意：DDL 程式碼需要進行一些小的更改才能使測試設置正常工作。
例如 tbl_tickets：ENUM 中的重複值 ‘’。一些 FK 的 REFERENCES 失敗，例如 tbl_tickets_ibfk_1。*