mysql查询以查找可用于预订日期范围与连续日期之间的汽车 [英] mysql query to find available car for booking between date range with continuous day
问题描述
我正在尝试开发CAR租赁系统.以下是我的数据库表架构
I am trying to develop a CAR renting system. Following are my DB table schema
CREATE TABLE IF NOT EXISTS `s_leases` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`vehicle_id` int(11) NOT NULL,
`start_date` date DEFAULT NULL,
`end_date` date DEFAULT NULL
) ;
样本数据
INSERT INTO `cs_leases`
(`id`, `vehicle_id`, `start_date` `end_date`) VALUES
(1, 1, '2018-03-07', '2018-03-12'),
(2, 1, '2018-03-17', '2018-03-21'),
(3, 1, '2018-03-24', '2018-03-30'),
(4, 3, '2018-03-07', '2018-03-10'),
(5, 3, '2018-03-12', '2018-03-15')
上表中包含汽车租赁数据
Above table contains car renting datils
CREATE TABLE IF NOT EXISTS `cs_lease_availabilities` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`lease_id` int(11) NOT NULL,
`vehicle_id` int(11) NOT NULL,
`date` date DEFAULT NULL,
`start_time` varchar(25) DEFAULT NULL,
`end_time` varchar(25) DEFAULT NULL
);
样本数据:
INSERT INTO `cs_lease_availabilities`
(`id`, `lease_id`,`vehicle_id`, `date`, `start_time`, `end_time`,`status`) VALUES
(1, 1, 1, '2018-03-07', '09:50 AM', '12:50 PM', 0),
(2, 1, 1, '2018-03-08', '01:51 PM', '04:50 PM',0),
(3, 1, 1, '2018-03-09', '01:06 PM', '10:00 PM',0),
(4, 1, 1, '2018-03-10', '09:00 AM', '10:00 AM', 0),
(5, 1, 1, '2018-03-11', '08:00 AM', '10:00 PM', 0),
(6, 1, 1, '2018-03-12', '12:00 PM', '10:00 PM',0),
(7,2, 1, '2018-03-17', '00:01 AM', '11:59 PM', 0),
(8, 2, 1, '2018-03-18', '00:01 AM', '11:59 PM', 0),
(9, 2, 1, '2018-03-19', '00:01 AM', '11:59 PM',0),
(10, 2, 1, '2018-03-20', '00:01 AM', '11:59 PM', 0),
(11, 2, 1, '2018-03-21', '00:01 AM', '11:59 PM',0),
(12, 3, 1, '2018-03-24', '00:01 AM', '11:59 PM',0),
(13, 3, 1, '2018-03-25', '00:01 AM', '11:59 PM', 0),
(14, 3, 1, '2018-03-26', '00:01 AM', '11:59 PM',0),
(15, 3, 1, '2018-03-27', '00:01 AM', '11:59 PM', 0),
(16, 3, 1, '2018-03-28', '00:01 AM', '11:59 PM',0),
(17, 3, 1, '2018-03-29', '00:01 AM', '11:59 PM', 0),
(18, 3, 1, '2018-03-30', '00:01 AM', '11:59 PM',0),
(19, 4, 3, '2018-03-07', '00:01 AM', '11:59 PM', 0),
(20, 4, 3, '2018-03-08', '00:01 AM', '11:59 PM', 0),
(21, 4, 3, '2018-03-09', '00:01 AM', '11:59 PM', 0),
(22, 4, 3, '2018-03-10', '00:01 AM', '11:59 PM', 0),
(23, 5, 3, '2018-03-12', '00:01 AM', '11:59 PM', 0),
(24, 5, 3, '2018-03-13', '00:01 AM', '11:59 PM', 0),
(25, 7, 3, '2018-03-14', '00:01 AM', '11:59 PM', 0),
(26, 7, 3, '2018-03-15', '08:01 AM', '11:00 PM', 0)
上表包含每天与时间相关的信息.
Above table contains the available time related info per day.
CREATE TABLE IF NOT EXISTS `cs_orders` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`lease_id` int(11) NOT NULL,
`vehicle_id` int(11) NOT NULL,
`start_date` date NOT NULL,
`end_date` date NOT NULL,
`status` tinyint(2) NOT NULL DEFAULT '0'
);
此表保存预订相关信息
以下表格可保存每天的预订时间信息.
Following table save booking time per day info.
CREATE TABLE IF NOT EXISTS `cs_order_availabilities` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`lease_id` int(11) NOT NULL,
`lease_availability_id` int(11) NOT NULL,
`cs_order_id` int(11) NOT NULL,
`vehicle_id` int(11) NOT NULL,
`date` date DEFAULT NULL,
`start_time` varchar(25) DEFAULT NULL,
`end_time` varchar(25) DEFAULT NULL
);
现在，我正在努力构建一个mysql查询，该查询可以返回具有连续日期可用性的日期范围之间的可用车辆租赁记录.
Now i am struggling to build a mysql query that can return available vehicle leases records between a date range with continuous date availability.
如果我尝试查找2018年3月8日至2018年3月15日之间的可用日期，则不应显示任何记录(cs租赁ID 1和2不是连续日期).
If i try to find availability date between 2018-03-08 to 2018-03-15, then no record should show (cs lease id 1 & 2 are not for continuous date).
任何帮助将不胜感激.
推荐答案
您可以尝试类似的东西，
You can try something like ,
SET @st = '2018-03-07';
SET @et = '2018-03-09';
SELECT cs.lease_id, COUNT(cs.`date`) AS ct
FROM cs_lease_availabilities cs
WHERE cs.`date` BETWEEN @st AND @et
GROUP BY cs.lease_id HAVING ct = TIMESTAMPDIFF(DAY , @st , @et)+1;
我希望这会返回您的预期结果.
I hope this will return your expected result.
这篇关于mysql查询以查找可用于预订日期范围与连续日期之间的汽车的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
