mysql - SQL 数据库 : Display available seats for each screening

Question

这是我的数据库:

CREATE TABLE customer (
    id INT AUTO_INCREMENT,
    email VARCHAR(64) NOT NULL,
    password VARCHAR(32) NOT NULL,
    PRIMARY KEY (id)
) ENGINE = InnoDB; 

CREATE TABLE movie (
    id INT AUTO_INCREMENT,
    title VARCHAR(64) NOT NULL,
    runtime TIME NOT NULL,
    certificate ENUM('U', 'PG', '12', '15', '18') NOT NULL,
    PRIMARY KEY (id)
) ENGINE = InnoDB; 

CREATE TABLE room (
    id INT AUTO_INCREMENT,
    roomNo ENUM ('Screen 1', 'Screen 2', 'Screen 3', 'Screen 4', 'Screen 5') NOT NULL,
    PRIMARY KEY (id)
) ENGINE = InnoDB;

CREATE TABLE seat (
    id INT AUTO_INCREMENT,
    row ENUM('A', 'B', 'C', 'D', 'E', 'F') NOT NULL,
    num ENUM('1', '2', '3', '4', '5', '6', '7', '8', '9', '10') NOT NULL,
    roomID INT,
    PRIMARY KEY (id),
    FOREIGN KEY (roomID) 
        REFERENCES room (id)
) ENGINE = InnoDB;

CREATE TABLE screening (
    id INT AUTO_INCREMENT,
    movieID INT,
    movieDate DATE NOT NULL,
    movieTime TIME NOT NULL,
    roomID INT,
    PRIMARY KEY (id),
    FOREIGN KEY (movieID) 
        REFERENCES movie (id),
    FOREIGN KEY (roomID) 
        REFERENCES room (id)
) ENGINE = InnoDB;

CREATE TABLE booking (
    id INT AUTO_INCREMENT,
    customerID INT,
    screeningID INT,
    seatID INT,
    PRIMARY KEY (id),
    FOREIGN KEY (customerID) 
        REFERENCES customer(id),
    FOREIGN KEY (screeningID) 
        REFERENCES screening (id),
    FOREIGN KEY (seatID) 
        REFERENCES seat (id)
) ENGINE = InnoDB;

这是我使用的查询:

SELECT s.id, s.row, s.num, s.roomID
FROM seat s
INNER JOIN screening scr
ON scr.roomID = s.roomID
LEFT JOIN booking b
ON s.id = b.seatID
WHERE b.seatID IS NULL AND scr.id = 6

所以我的问题是对于筛选 id = 2，roomID 是 1(意思是屏幕 1)。假设客户预订了 seatID“1”，这意味着 A 行，用于放映 1 的座位号 1。当我运行此查询以显示另一个放映 ID 的可用座位时，例如它的 6 和房间相同(屏幕 1)，它会显示除 seatID 1 之外的所有座位，因为它是由另一位客户预订的。

我想要的是每个放映都有自己的座位，问题是每个房间都有。因此，如果另一个放映(电影)碰巧使用同一个房间，它就会与另一个使用同一个房间的放映发生冲突。

Answer 1

以下内容应为您提供可用于 Screening 的所有 Seats 的列表，id 为 sentinelValue ...

SELECT id AS seatID
FROM seat
WHERE roomID = ( SELECT roomID
                 FROM screening
                 WHERE screeningID = sentinelValue )
  AND id NOT IN ( SELECT seatID
                  FROM booking
                  WHERE screeningID = sentinelValue );

我的逻辑是这样的……

要确定哪些 Seats 仍可用于某个 Screening 我们必须首先知道哪些 Seats 最初可用于该 Screening ，即分配给Screening的Room的Seats。

为此，我们从已知值 screening.id/screeningID 开始，我将其称为 sentinelValue。

有了这个已知值，我们可以使用以下 SQL 确定与 screening 关联的 room.id/roomID...

SELECT roomID
FROM screening
WHERE screeningID = sentinelValue

数据库只明确记录哪些座位被预订用于放映，但没有明确记录哪些座位 未预订。幸运的是，我们可以通过将 booked seats 列表与分配给该放映的座位的完整列表，这是分配给该房间的座位列表。

要获取预订的座位列表，我使用了以下 SQL...

SELECT seatID
FROM booking
WHERE screeningID = sentinelValue

然后所有需要做的就是获取分配给放映的房间的座位列表NOT 尚未预订。为此，我使用了答案开头的 SQL。

如果您有任何问题或意见，请随时发表相应的评论。