php - 计算两个公寓都无人居住的时间-MySQL

我有2张桌子报告2个假期公寓的入住情况。

列是
ID，到达周，出发周。简单的SMALLINT

我已经有了一个系统，可以在该系统中按开始周和结束周指定一年中的期间，并查看该期间内单个公寓的占用周数。这可以正常工作，如下所示，例如，查看第5周到第22周之间1号公寓被占用了多少周...

$begin=5; // start of analysis period
$end=22;  // end of analysis period

 // ###############################################################################
// Apartment 1
// ##################################################################################
$totalperiod=0;$arrivalweek=0;$departureweek=0;$period=0;
foreach($db->query('SELECT   id, arrivalweek, departureweek FROM apartment1  WHERE departureweek > '.$begin.' AND arrivalweek < '.$end.'  ORDER BY id ASC') as $record)
{
$arrivalweek=$record['arrivalweek'];
$departureweek=$record['departureweek'];
if ($arrivalweek<$begin) {$arrivalweek=$begin;} // we might already be in occupancy.
if($departureweek>$end){$departureweek=$end;}   // we might not have vacated yet.
$period=$departureweek-$arrivalweek; // period for this sector
$totalperiod=$totalperiod+$period;  // add all sectors to get total period
}
echo "Time range from ".$begin." to ".$end.", for apartment 1<br>";
echo $totalperiod." = total weeks occupied for apartment 1 in that period<br>";
// ##################################################################################
// End of individual apartment occupancy code
// ##################################################################################

现在，我想知道那段时间两间公寓都没人住，并且一直在努力寻找解决方案。在指定的时间段内1号公寓未被占用和2号公寓未被占用的总周数。

任何线索都欢迎！

谢谢

G

最佳答案

未测试（没有测试数据），但可能是这样的：

SELECT COUNT(*)
FROM
(
    SELECT Tens.a * 10 + Units.a AS weekNumber
    FROM
    (SELECT 0 AS a UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) Tens,
    (SELECT 0 AS a UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) Units
    HAVING weekNumber BETWEEN $begin AND $end
) allWeeks
LEFT OUTER JOIN apartment1 ON allWeeks.weekNumber BETWEEN apartment1.arrivalweek AND  apartment1.departureweek
LEFT OUTER JOIN apartment2 ON allWeeks.weekNumber BETWEEN apartment2.arrivalweek AND  apartment2.departureweek
WHERE apartment1.id IS NULL
AND apartment2.id IS NULL

第一个子查询获取$ begin和$ end之间的星期列表。

这与每个公寓的预订表保持联系（请注意，您可能应该为两个公寓都有一个表，并在其中定义一列与之相关的一列）。然后，WHERE子句将忽略在联接上找到匹配项的任何行。