我有一張帶有度假屋的桌子，有一些可用性（列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
uj5u.com熱心網友回復：
例如，查找 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;
uj5u.com熱心網友回復：
您可以嘗試使用條件聚合函式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如果您的表中有重復的天數有 1 個值，那么在聚合函式中只會計算一次，但是如果您在遇到這種情況時想要計算多個，則可以DISTINCT從聚合函式中洗掉。
編輯
由于您的和列有UNIQUE約束，您不需要在聚合函式上使用。unit_iddateDISTINCT
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)
轉載請註明出處，本文鏈接：https://www.uj5u.com/qiye/427351.html
上一篇：如何解決mysql中的間隙和孤島問題，以解決零值的間隙和非零值的孤島
下一篇：laravel8中的多用戶角色