查找两个日期之间每个记录都有特定值的所有记录

Question

我有一张表，里面有一些度假屋，它们有一些可用性(列value，值1表示可用(。如何查找两个日期之间可用的所有房屋(列unit_id(。

表格

CREATE TABLE `houseavailability` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`date` varchar(100) DEFAULT NULL,
`value` varchar(100) DEFAULT NULL,
`unit_id` varchar(100) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `houseavailability_unit_id_IDX` (`unit_id`,`date`) USING BTREE,
KEY `houseavailability_unit_id_IDX_solo` (`unit_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=16648943 DEFAULT CHARSET=latin1;

测试数据

INSERT INTO houseavailability
(id, `date`, value, unit_id)
VALUES(15814115, '2022-07-23', '1', '1004004');
INSERT INTO houseavailability
(id, `date`, value, unit_id)
VALUES(15814116, '2022-07-24', '1', '1004004');
INSERT INTO houseavailability
(id, `date`, value, unit_id)
VALUES(15814117, '2022-07-25', '1', '1004004');
INSERT INTO houseavailability
(id, `date`, value, unit_id)
VALUES(15814118, '2022-07-26', '1', '1004004');
INSERT INTO houseavailability
(id, `date`, value, unit_id)
VALUES(15814119, '2022-07-27', '1', '1004004');
INSERT INTO houseavailability
(id, `date`, value, unit_id)
VALUES(15814120, '2022-07-28', '1', '1004004');
INSERT INTO houseavailability
(id, `date`, value, unit_id)
VALUES(15814121, '2022-07-29', '1', '1004004');
INSERT INTO houseavailability
(id, `date`, value, unit_id)
VALUES(15814122, '2022-07-30', '0', '1004004');

尝试

SELECT houseavailability.*
FROM houseavailability
WHERE houseavailability.date BETWEEN '2022-07-23' AND '2022-07-30'
AND houseavailability.unit_id = 1004004;

http://sqlfiddle.com/#！9/094547/2

Answer 1

例如，查找在整个指定时间段内可用的unit_id。

SELECT unit_id
FROM houseavailability
WHERE date BETWEEN '2022-07-23' AND '2022-07-30'
GROUP BY unit_id
HAVING sum(value) = datediff('2022-07-30','2022-07-23') + 1;

Answer 2

您可以尝试使用HAVING中的条件聚合函数来比较是否所有为true的行都符合您的日期条件。

查询1：

SELECT unit_id
FROM houseavailability
WHERE date BETWEEN '2022-07-23' AND '2022-07-30'
GROUP BY unit_id
HAVING COUNT(DISTINCT date) = COUNT(DISTINCT CASE WHEN value = '1' THEN date END)

结果：

如果表中有重复的天数，则聚合函数中的DISTINCT将只计数一次，但如果您希望在遇到这种情况时计数多次，则可以从聚合函数中删除DISTINCT。

编辑

由于unit_id和date列中存在UNIQUE约束，因此不需要在聚合函数中使用DISTINCT。

SELECT unit_id
FROM houseavailability
WHERE date BETWEEN '2022-07-23' AND '2022-07-30'
GROUP BY unit_id
HAVING COUNT(*) = COUNT(CASE WHEN value = '1' THEN date END)