我有两个数据库：
Reservation
------------------------------------------------
| ID | RoomID | RoomName | BookingDate |
|-----------------------------------------------
| 1 | 77 | Gold | 2017-12-05 00:00:00 |
| 2 | 88 | Crystal | 2017-12-11 00:00:00 |
| 3 | 88 | Crystal | 2017-12-20 00:00:00 |
| 4 | 99 | Diamond | 2017-12-01 00:00:00 |
| 5 | 77 | Gold | 2017-12-04 00:00:00 |
| 6 | 77 | Gold | 2017-12-15 00:00:00 |
-------------------------------------------------
Room
-----------------
| ID | RoomName |
-----------------
| 77 | Gold |
| 88 | Crystal |
| 99 | Diamond |
-----------------
我试图计算日期范围内房间的记录数。假定输出为：
------------------------------------------------------
| Date | Gold | Crystal | Diamond |
------------------------------------------------------
| 1.12.2017 - 3.12.2017 | 0 | 0 | 1 |
| 4.12.2017 - 10.12.2017 | 2 | 0 | 0 |
| 11.12.2017 - 17.12.2017 | 1 | 1 | 0 |
| 18.12.2017 - 24.12.2017 | 0 | 1 | 0 |
| 25.12.2017 - 31.12.2017 | 0 | 0 | 0 |
------------------------------------------------------
我的编码如下：
<table>
<tr><th> Date </th>
<?php
$sql = mysql_query("SELECT * FROM Room", $con);
while($record = mysql_fetch_array($sql)) {
?>
<td> <?php echo $record['RoomName']?> </td>
<?php
}
?>
</tr>
<tr> <td>
<?php
$week = date("W", strtotime($_GET['Year'] . "-" . $_GET['Month'] ."-01")); // weeknumber of first day of month
$startRange = date("d.m.Y", strtotime($_GET['Year'] . "-" . $_GET['Month'] ."-01")); // first day of month
echo $startRange . " to ";
$unix = strtotime($_GET['Year']."W".$week ."+1 week");
while(date("m", $unix) == $_GET['Month']){ // keep looping/output of while it's correct month
$endRange = date("d.m.Y", $unix-86400);
echo $endRange . "</td>"; // Sunday of previous week
//counting for Gold Room
$res=mysql_query("SELECT * FROM Reservation WHERE (BookingDate>='".$startRange."' AND BookingDate<='".$endRange."') AND RoomID='77' ");
$count=mysql_num_rows($res);
echo "<td align='center'>" . $count . "</td>";
//end counting part
//counting part Crystal Room
$res=mysql_query("SELECT * FROM Reservation WHERE (BookingDate>='".$startRange."' AND BookingDate<='".$endRange."') AND RoomID='88' ");
$count=mysql_num_rows($res);
echo "<td align='center'>" . $count . "</td>";
//end counting part
//counting part Diamond Room
$res=mysql_query("SELECT * FROM Reservation WHERE (BookingDate>='".$startRange."' AND BookingDate<='".$endRange."') AND RoomID='99' ");
$count=mysql_num_rows($res);
echo "<td align='center'>" . $count . "</td>";
//end counting part
echo "</tr><tr> <td align='center'>";
$lastStartRange = date("d.m.Y", $unix);
echo $lastStartRange ." to "; // this week's monday
$unix = $unix + (86400*7);
}
$lastEndRange = date("t.m.Y", strtotime($_GET['Year'] . "-" . $_GET['Month']));
echo $lastEndRange; //echo last day of month
?>
</td> <td align="center">
<?php
//counting for Gold Room
$res=mysql_query("SELECT * FROM Reservation WHERE (BookingDate>='".$lastStartRange."' AND BookingDate<='".$lastEndRange."') AND RoomID='77' ");
$count=mysql_num_rows($res);
echo "<td align='center'>" . $count . "</td>";
//end counting part
//counting part Crystal Room
$res=mysql_query("SELECT * FROM Reservation WHERE (BookingDate>='".$lastStartRange."' AND BookingDate<='".$lastEndRange."') AND RoomID='88' ");
$count=mysql_num_rows($res);
echo "<td align='center'>" . $count . "</td>";
//end counting part
//counting part Diamond Room
$res=mysql_query("SELECT * FROM Reservation WHERE (BookingDate>='".$lastStartRange."' AND BookingDate<='".$lastEndRange."') AND RoomID='99' ");
$count=mysql_num_rows($res);
echo "<td align='center'>" . $count . "</td>";
//end counting part
?>
上面代码的问题是，当我选择
month = January时，它将显示日期范围01.01.2017-31.12.2017。至于记录计数，它与
Reservation的数据库表中的记录不匹配。我不知道如何简化代码。对我的问题有任何建议或解决方案吗？从
$_GET['Month']和$_GET['Year']中检索日期范围的月份和年份。日期范围应根据所选的月份和年份动态更改。日期范围是任何选定月份的星期范围，因此没有固定的星期范围。
表格标题上的房间名称应从
数据库表“ Room”。
计数应计算日期范围内特定房间的预订数量。
谢谢..
最佳答案
好吧，您可以尝试构建以下查询
SELECT '1.12.2017 - 3.12.2017',
sum(CASE WHEN room_id = 77 THEN 1 ELSE 0 END) AS Gold,
sum(CASE WHEN room_id = 88 THEN 1 ELSE 0 END) AS Crystal,
sum(CASE WHEN room_id = 99 THEN 1 ELSE 0 END) AS Dimond,
FROM Reservations
WHERE BookingDate BETWEEN '2017-12-01 00:00:00' AND '2017-12-03 23:59:59'
UNION
SELECT '4.12.2017 - 10.12.2017',
sum(CASE WHEN room_id = 77 THEN 1 ELSE 0 END) AS Gold,
sum(CASE WHEN room_id = 88 THEN 1 ELSE 0 END) AS Crystal,
sum(CASE WHEN room_id = 99 THEN 1 ELSE 0 END) AS Dimond,
FROM Reservations
WHERE BookingDate BETWEEN '2017-12-04 00:00:00' AND '2017-12-10 23:59:59'
UNION
SELECT '11.12.2017 - 17.12.2017',
sum(CASE WHEN room_id = 77 THEN 1 ELSE 0 END) AS Gold,
sum(CASE WHEN room_id = 88 THEN 1 ELSE 0 END) AS Crystal,
sum(CASE WHEN room_id = 99 THEN 1 ELSE 0 END) AS Dimond,
FROM Reservations
WHERE BookingDate BETWEEN '2017-12-11 00:00:00' AND '2017-12-17 23:59:59'
UNION
SELECT '18.12.2017 - 24.12.2017',
sum(CASE WHEN room_id = 77 THEN 1 ELSE 0 END) AS Gold,
sum(CASE WHEN room_id = 88 THEN 1 ELSE 0 END) AS Crystal,
sum(CASE WHEN room_id = 99 THEN 1 ELSE 0 END) AS Dimond,
FROM Reservations
WHERE BookingDate BETWEEN '2017-12-18 00:00:00' AND '2017-12-24 23:59:59'
UNION
SELECT '25.12.2017 - 31.12.2017 ',
sum(CASE WHEN room_id = 77 THEN 1 ELSE 0 END) AS Gold,
sum(CASE WHEN room_id = 88 THEN 1 ELSE 0 END) AS Crystal,
sum(CASE WHEN room_id = 99 THEN 1 ELSE 0 END) AS Dimond,
FROM Reservations
WHERE BookingDate BETWEEN '2017-12-25 00:00:00' AND '2017-12-31 23:59:59'
您需要使用所需的输出参数来建立这样的查询。这样，您将可以一次获得输出。现在，这里唯一的问题似乎是RoomID和RoomName看起来不是动态的。但是同样，您需要在查询中动态构建它。因此，如果明天要添加另一个房间，它也将出现在查询中。
关于php - 如何计算日期范围内的记录及其条件，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/47765166/