MySQL - 查找属于给定月份的日期范围内的天数

Question

我有一个表格，其中显示了人们在某个日期范围内(始终最多为 5 天)从事的工作。

是否可以按月分割日期范围的内容？

我的表格(简化版)如下所示:

+---------------------+---------------------+--------------+
| datefrom            | dateto              | dailypayrate |
+---------------------+---------------------+--------------+
| 2014-05-05 00:00:00 | 2014-05-10 00:00:00 |        78.00 |
| 2014-04-07 00:00:00 | 2014-04-14 00:00:00 |        59.30 |
| 2014-02-10 00:00:00 | 2014-02-14 00:00:00 |        82.66 |
| 2014-04-28 00:00:00 | 2014-05-02 00:00:00 |        91.00 |<-- !
| 2014-01-28 00:00:00 | 2014-01-31 00:00:00 |        65.00 |
+---------------------+---------------------+--------------+

我想显示给定月份每行支付的总额，例如对于 4 月的第 4 行，它将显示 273，即 91 乘以 4 月工作的 3 天的每日工资(即使该行显示 5 天)。

Answer 1

你可以在 SQL 中做你想做的事，但它不是很可爱。计算日期范围不包括月份变化的行非常容易 (WHERE MONTH(timefrom) = MONTH(timeto))。只需按照 Strawberry 的建议执行 DATEDIFF。

SELECT 
        MONTH(datefrom) as month_number, 
        DATEDIFF(dateto, datefrom) as days_worked, 
        DATEDIFF(dateto, datefrom)*dailypayrate as salary
FROM my_table
WHERE MONTH(datefrom) = MONTH(dateto)

对于包含月份变化的日期范围，我们需要将这些行分成两行:一行计算 MONTH(timefrom)，另一行计算 MONTH(timeto)。这里是针对MONTH(timeto)的，另外一个就是简单的减法。

SELECT 
        MONTH(dateto) as month_number, 
        MINUTE(dateto)/(60*24) + HOUR(dateto)/24 + DAY(dateto)-1 as days_worked, 
        (MINUTE(dateto)/(60*24) + HOUR(dateto)/24 + DAY(dateto)-1)*dailypayrate as salary
FROM my_table
WHERE MONTH(datefrom) != MONTH(dateto)

然后我们将这 3 个查询 UNION，按 month_number 分组，它应该给出正确的数字。

http://sqlfiddle.com/#!2/120766/2