我在理解和避免查询中的一个问题时遇到了一些问题。
我在查询要在选定的时间段内选择可用的汽车,但是却遇到了问题。如果是租车,那是我选择的时期。在这种情况下,我所有的3辆车都是从2015-04-20到2015-04-25租用的。唯一的其他预订是从2015-04-26至2015-04-30的Car 1。
当我的查询随后在2015年4月20日至2015年4月25日的计时器时段内免费返回Car1时出现问题,而实际上仍在进行另一个预订。
之所以返回car1,是因为2015年4月26日至2015年4月30日的预订中有car1,并且满足了要求,因为它不在租赁期内。
我当前的逻辑。在以下情况下,汽车可以免费使用:
// Case 1: dropoff < r.pickup
// Case 2: pickup > r. dropoff
// Case 3: IF FK_CarID == Null
从我的文字中可能很难理解,所以这里有一个SQLfiddle链接:http://sqlfiddle.com/#!9/13392/1
有用于创建所有表和我的查询的代码。
而且您可以看到,即使采用1,它也将返回1。因此需要以某种方式使其忘记1(如果在该时间段内已取1)。也许通过使用“ between”,但是当发现1不是选项时,无法弄清楚如何阻止它返回其他1。
这也是表和查询的图像,用于概述目的:
Mysql Reservation Table and flawed Query
为了便于使用,以下是查询:
SELECT DISTINCT c.CarID FROM Cars c
LEFT JOIN Reservations r ON (c.CarID = r.FK_car)
WHERE '2015-04-25' < r.pickUpDate
OR r.FK_car IS NULL
OR '2015-04-20' > r.dropOffDate;
并创建表Reservations和Cars:
CREATE TABLE Cars (
carID INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
model VARCHAR(255) NOT NULL,
make VARCHAR(255) NOT NULL,
creationYear INT(4) NOT NULL,
licenseNum VARCHAR(7) NOT NULL,
FK_station INT(6) UNSIGNED,
CONSTRAINT FOREIGN KEY (FK_station)
REFERENCES Stations(stationID)
ON UPDATE CASCADE
ON DELETE SET NULL
) ENGINE=InnoDB;
CREATE TABLE Reservations (
ReservationID INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
pickUpDate DATE NOT NULL,
dropOffDate DATE NOT NULL,
FK_customer INT(6) UNSIGNED,
FK_car INT(6) UNSIGNED,
CONSTRAINT FOREIGN KEY (FK_customer)
REFERENCES Customers(customerID)
ON UPDATE CASCADE
ON DELETE SET NULL,
CONSTRAINT FOREIGN KEY (FK_car)
REFERENCES Cars(carID)
ON UPDATE CASCADE
ON DELETE SET NULL
) ENGINE=InnoDB;
希望您能帮助我解决有缺陷的逻辑。我对MySQL来说还很陌生,因此以正确的方式思考仍然是一个挑战。
最佳答案
这应该工作:
SELECT distinct(car.carID) FROM Cars car
where car.carId not in (
select r.FK_car from Reservations r
WHERE (r.pickUpDate >= '2015-04-20' and r.pickUpDate <= '2015-04-25')
and (r.dropOffDate >= '2015-04-20' and r.dropOffDate <= '2015-04-25')
or (r.pickUpDate < '2015-04-20' and r.dropOffDate > '2015-04-25')
)
也就是说,某些不属于预定的汽车:
接送日期在范围内
拾取开始于范围之前,结束于范围之后。