如何计算日期范围内的记录及其标准

Question

我有两个数据库：

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  |
-----------------

我试着在日期的范围内计算房间的记录数量。预期的输出如下：

，日期，日期，黄金，水晶，钻石，\x{e76f}\x{e76f}

我的编码如下：

<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”中检索。
• 点票应计算在日期范围内的特定房间的预订数量。

谢谢..

Answer 1

那么，您可以尝试构建这样的查询

 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看上去不是动态的。但同样，您需要在查询中动态地构建它。因此，如果明天添加了另一个房间，它也会出现在查询中。

Answer 2

我建议有一个日历表(至少是日期表和weekno表)，因此假设每个日期表都有一行日历表：

select
   c.weekno, room.id, min(c.dt) startweek_dt, max(c.dt) endweek_dt, count(r.bookingdate)
from calendar_table c
cross join room
left join reservation r on c.caldate = r.bookingdate and room.id = r.room_id
where c.dt >= '2017-01-01' and c.dt < '2017-02-01'
group by week(c.weekno), room.id

这是一个关于建立日历表的讨论

Answer 3

您可以使用存储过程代替对日期的动态绑定的查询。

CREATE PROCEDURE `Reservation_data` (p_date1 date,p_date2 date)
BEGIN

SELECT concat(date_format(p_date1,'%d.%m.%Y'),' - ',date_format(p_date2,'%d.%m.%Y')), 
        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 p_date1 AND p_date2
UNION 
SELECT concat(date_format(p_date1,'%d.%m.%Y'),' - ',date_format(p_date2,'%d.%m.%Y')), 
        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 p_date1 AND p_date2
UNION
SELECT concat(date_format(p_date1,'%d.%m.%Y'),' - ',date_format(p_date2,'%d.%m.%Y')), 
        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 p_date1 AND p_date2
UNION
SELECT concat(date_format(p_date1,'%d.%m.%Y'),' - ',date_format(p_date2,'%d.%m.%Y')), 
        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 p_date1 AND p_date2
UNION 
SELECT concat(date_format(p_date1,'%d.%m.%Y'),' - ',date_format(p_date2,'%d.%m.%Y')),  
        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 p_date1 AND p_date2;

END$$