预订检查空房情况

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.1

SET 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 qtyFROM booking_orders oLEFT JOIN booking_order_room i on o.id = i.order_idLEFT JOIN booking_rooms r on i.room_id = r.idwhere(checkin<'2020-07-17' and checkout>='2020-07-17') -- overlap at the endOR (checkin<='2020-07-15' and checkout>'2020-07-15') -- overlap at the startOR (checkin>='2020-07-15' and checkout<='2020-07-17') -- complete overlapGROUP BY r.id);SELECT br.name as name, br.quantity as quantity, br.input as input, br. price as price, t1.qty&nbsp;FROM booking_rooms brLEFT JOIN t1 ON t1.id = br.room_idORDER BY br.name DESC