预订检查空房情况

Question

我正在创建一个预订房间的系统。我需要找出日期范围内的可用房间数量。退房当天房间应该可用。到目前为止，我只能找到日期范围内的订单，但即使是这些订单也无法正常工作。这有效 2020-06-27 - 2020-07-05这不起作用 2020-06-28 - 2020-07-05目标是找出日期范围内的空闲房间数量，然后提供订单。我在这里阅读了很多主题，但没有想出解决方案。我使用 PHP 和 MySQL-- phpMyAdmin SQL Dump-- version 4.7.4-- https://www.phpmyadmin.net/---- Počítač: 127.0.0.1-- Vytvořeno: Sob 27. čen 2020, 10:24-- Verze serveru: 10.1.28-MariaDB-- Verze PHP: 7.2.1SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";SET AUTOCOMMIT = 0;START TRANSACTION;SET time_zone = "+00:00";/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;/*!40101 SET NAMES utf8mb4 */;---- Databáze: `rezervace`---- ------------------------------------------------------------ Struktura tabulky `booking_orders`--CREATE TABLE `booking_orders` (  `id` int(11) NOT NULL,  `number` int(11) NOT NULL,  `checkin` date NOT NULL,  `checkout` date NOT NULL,  `first_name` varchar(255) COLLATE utf8_czech_ci NOT NULL,  `last_name` varchar(255) COLLATE utf8_czech_ci NOT NULL,  `email` varchar(255) COLLATE utf8_czech_ci NOT NULL,  `phone` varchar(20) COLLATE utf8_czech_ci NOT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci;---- Vypisuji data pro tabulku `booking_orders`--INSERT INTO `booking_orders` (`id`, `number`, `checkin`, `checkout`, `first_name`, `last_name`, `email`, `phone`) VALUES(74, 0, '2020-06-27', '2020-06-28', '', '', '', ''),(75, 0, '2020-06-27', '2020-06-29', '', '', '', ''),(76, 0, '2020-06-27', '2020-07-01', '', '', '', ''),(77, 0, '2020-06-28', '2020-06-29', '', '', '', '');-- ------------------------------------------------------------ Struktura tabulky `booking_order_room`--

Answer 1

附上我最终使用的解决方案。也许可以采取不同的做法。

DROP TABLE IF EXISTS t1;

CREATE TEMPORARY TABLE t1 as (

SELECT r.name as name, r.id, sum(i.quantity) as qty

FROM booking_orders o

LEFT JOIN booking_order_room i on o.id = i.order_id

LEFT JOIN booking_rooms r on i.room_id = r.id

where

(checkin<'2020-07-17' and checkout>='2020-07-17') -- overlap at the end

OR (checkin<='2020-07-15' and checkout>'2020-07-15') -- overlap at the start

OR (checkin>='2020-07-15' and checkout<='2020-07-17') -- complete overlap

GROUP BY r.id

);

SELECT br.name as name, br.quantity as quantity, br.input as input, br. price as price, t1.qty 

FROM booking_rooms br

LEFT JOIN t1 ON t1.id = br.room_id

ORDER BY br.name DESC