mysql - 需要一个聚合的 MySQL 选择，它几乎可以跨日期范围迭代并返回账单

Question

我有一个名为 rbsess 的 MySQL 表，其中包含列 RBSessID (key)、ClientID (int)、RBUnitID (int)、RentAmt (fixed-point int)、RBSessStart (DateTime) 和 PrevID (int，对 RBSessID 的引用)。

它不是事务性的或链接的。当客户搬进房间时，它会跟踪什么，以及搬入时的租金是多少。查找特定客户在特定日期的租金是多少的查询是：

SET @DT='Desired date/time'
SET @ClientID=Desired client id

SELECT a.RBSessID
     , a.ClientID
     , a.RBUnitID
     , a.RentAmt
     , a.RBSessStart
     , b.RBSessStart AS RBSessEnd
     , a.PrevID 
  FROM rbsess a 
  LEFT 
  JOIN rbsess b 
    ON b.PrevID=a.RBSessID 
 WHERE a.ClientID=@ClientID 
   AND (a.RBSessStart<=@DT OR a.RBSessStart IS NULL) 
   AND (b.RBSessStart>@DT OR b.RBSessStart IS NULL);

这将输出如下内容：

+----------+----------+----------+---------+---------------------+-----------+--------+
| RBSessID | ClientID | RBUnitID | RentAmt | RBSessStart         | RBSessEnd | PrevID |
+----------+----------+----------+---------+---------------------+-----------+--------+
|        2 |        4 |        1 |   57500 | 2020-11-22 00:00:00 | NULL      |      1 |
+----------+----------+----------+---------+---------------------+-----------+--------+

我也有

SELECT * FROM rbsess WHERE rbsess.ClientID=@ClientID AND rbsess.PrevID IS NULL; //for finding the first move in date
SELECT TIMESTAMPDIFF(DAY,@DT,LAST_DAY(@DT)) AS CountDays; //for finding the number of days until the end of the month
SELECT DAY(LAST_DAY(@DT)) AS MaxDays;  //for finding the number of days in the month
SELECT (TIMESTAMPDIFF(DAY,@DT,LAST_DAY(@DT))+1)/DAY(LAST_DAY(@DT)) AS ProRateRatio; //for finding the ratio to calculate the pro-rated rent for the move-in month
SELECT ROUND(40000*(SELECT (TIMESTAMPDIFF(DAY,@DT,LAST_DAY(@DT))+1)/DAY(LAST_DAY(@DT)) AS ProRateRatio)) AS ProRatedRent; //for finding a pro-rated rent amount based on a rent amount.

我无法将所有这些放在一起以形成一个查询，该查询可以根据开始日期和可选的结束日期输出按比例计算的全部租金金额，并在该期间每个月的单个报表中输出所有欠租金额。我可以添加收到的付款表并在之后将其集成，只是很难在 MySQL 查询中使用这个看似简单的现实世界概念。我正在使用带有 MySQL 后端的 php。临时表作为中间查询是完全可以接受的。

即使是轻推也会有帮助。我对 MySQL 查询没有超级经验，只是基本的 CREATE、SELECT、INSERT、DROP 和 UPDATE。

GMB 要求的示例：

//Example data in rbsess table:
+----------+----------+----------+---------+---------------------+--------+
| RBSessID | ClientID | RBUnitID | RentAmt | RBSessStart         | PrevID |
+----------+----------+----------+---------+---------------------+--------+
|        1 |        4 |        1 |   40000 | 2020-10-22 00:00:00 |   NULL |
|        2 |        4 |        1 |   57500 | 2020-11-22 00:00:00 |      1 |
|        3 |        2 |        5 |   40000 | 2020-11-29 00:00:00 |   NULL |
+----------+----------+----------+---------+---------------------+--------+

预期结果将是每月欠租金额的清单，包括一个月内部分入住的按比例分摊的金额，从几个月的日期范围开始。例如，对于上面的示例数据，来自 ClientID=4 的客户的跨越 2020 年全年的日期范围，查询将在类似于以下的范围内生成每个月的金额：

Month     | Amt
2020-10-1 | 12903
2020-11-1 | 45834
2020-12-1 | 57500

标签： mysqldynamicaggregation