原问题：
I have a table with vacation houses which have some availability (column value, value 1 means available ). How can I find all houses (column unit_id) that are are available between 2 dates.
Table
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;Test Data
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');Attempt
SELECT houseavailability.*
FROM houseavailability
WHERE houseavailability.date BETWEEN '2022-07-23' AND '2022-07-30'
AND houseavailability.unit_id = 1004004;采纳答案
You can try to use the condition aggregate function in HAVING to compare whether all the rows for which this is true between your date condition.
Query 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)Results：
DISTINCT which is in aggregate function will count only once, if there are duplicate days have 1 value in your tables, but if you want to count multiple when you met that situation you can remove DISTINCT from the aggregate function.
EDIT：
Due to there being a UNIQUE constraint from your unit_id and date columns, you don’t need to use DISTINCT on your aggregate function.
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)问题解析：
表houseavailability 中有date、value 字段，date表日期，value为1的时候代表可用。需要查出指定日期间value都为1的数据。比如查询2022-07-23至2022-07-30则没有结果，因为2022-07-23那一天的value值为0，如果查询2022-07-23至2022-07-29则结果为1004004，因为这段时间内的value值都为1。解决的思路主要判断指定日期间的数据量和满足value=1的数据量是否相等，如果相等则满足条件。因为示例数据从日期没有重复值所以可以不加Distinct关键字进行去重。具体运行结果查看 dbfiddle
本文根据StackOverflow翻译而来，不代表烟海拾贝立场，如若转载，请注明出处：https://somirror.com/556.html
