我正在創建一個預訂房間的系統。我需要找出日期范圍內的可用房間數量。退房當天房間應該可用。到目前為止，我只能找到日期范圍內的訂單，但即使是這些訂單也無法正常工作。這有效 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`--
1 回答
斯蒂芬大帝
TA貢獻1827條經驗 獲得超8個贊
附上我最終使用的解決方案。也許可以采取不同的做法。
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
- 1 回答
- 0 關注
- 143 瀏覽
添加回答
舉報
0/150
提交
取消