mysql - 如何更新查询以使用两个日期和 BETWEEN 计算 SUM 或 COUNT？

Question

我当前的查询是这样的:

     SELECT
      CAST(COUNT(`MID`) AS UNSIGNED) AS Y,
      CONCAT(
          LEFT(MONTHNAME(`date`),
          3),
          ' ',
          YEAR(`date`)
      ) AS label
  FROM
      `reservations`
  WHERE
      `MID` = 22 AND YEAR(`date`) = YEAR(CURDATE())
  GROUP BY
      CONCAT(
          LEFT(MONTHNAME(DATE),
          3),
          ' ',
          YEAR(`date`)
      ),
      YEAR(DATE),
      MONTH(DATE)
  ORDER BY
      YEAR(`date`),
      MONTH(`date`) ASC

它会生成以下结果，我们在 Google 图表中使用这些结果来显示每月的预订数量。问题是我们只能获取创建预订的次数，而不是开始日期 (date) 和结束日期 (dateLast) 之间的天数。

 Y      label
 ________________
 22      Feb 2019
 28      Mar 2019
 15      Apr 2019
 3       May 2019
 5       Jun 2019
 2       Jul 2019
 1       Aug 2019
 1       Oct 2019
 2       Nov 2019
 9       Dec 2019

我一直在尝试以下更新，但收到与 BETWEEN 运算符相关的错误:

 SELECT
     CAST(COUNT(`mid`) AS UNSIGNED BETWEEN `date` AND `dateLast`) AS D, CONCAT(
         LEFT(MONTHNAME(DATE),
         3), ' ',  YEAR(DATE) ),
     CAST(COUNT(`mid`) AS UNSIGNED) AS Y,
     CONCAT(
         LEFT(MONTHNAME(DATE),
         3),
         ' ',
         YEAR(DATE)
     ) AS label
 FROM
     `reservations`
 WHERE
     `mid` = 22 AND YEAR(DATE) = YEAR(CURDATE())
 GROUP BY
     CONCAT(
         LEFT(MONTHNAME(DATE),
         3),
         ' ',
         YEAR(DATE)
     ),
     YEAR(DATE),
     MONTH(DATE)
 ORDER BY
     YEAR(DATE),
     MONTH(DATE) ASC

MySQL 说:文档

您的 SQL 语法有误；检查与您的 MySQL 服务器版本对应的手册，了解在 'BETWEEN date AND dateLast) AS D, CONCAT(LEFT(MONTHNAME(DATE),' 附近使用的正确语法在第 2 行

目标是获得 AND 之间保留的所有日期的总和，包括 date 到 dateLast 注意:dateLast 不按实际情况计算在内结账日期。也许这对于 SQL 查询来说太复杂，应该在 PHP 中作为一系列子例程进行处理？

Answer 1

如果您不需要将一次预订的天数拆分为多个月，则可以仅使用 SUM(DATEDIFF(dateLast, date))

select year(date) y, month(date) m, sum(datediff(dateLast, date)) c
from reservations
group by y, m
order by y, m

db-fiddle

如果你想拆分它们，那么我希望你的版本(MySQL 8+或MariaDB 10.2+)支持递归查询。在这种情况下，您可以将日期范围扩展到该范围内每天一行并对其进行计数:

with recursive rcte as (
  select date, dateLast 
  from reservations
  where dateLast > date -- optional
  union all
  select rcte.date + interval 1 day, rcte.dateLast
  from rcte
  where rcte.date < rcte.dateLast - interval 1 day
)
  select year(date) y, month(date) m, count(*) c
  from rcte
  group by y,m
  order by y,m

db-fiddle