时间戳

首页 > 解决方案 > 如何使用两个日期和 BETWEEN 将查询更新为计算 SUM 或 COUNT？

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

问题描述

我目前的查询是这样的：

     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 图表中使用这些结果来显示每月的预订数量。问题是我们只获得创建预订的次数，而不是开始日期（日期）和结束日期（日期最后）之间的天数。

 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 服务器版本相对应的手册，以在第 2 行的“BETWEEN dateAND dateLast) AS D, CONCAT(LEFT(MONTHNAME(DATE),”附近使用正确的语法

目标是获得在 AND 之间保留的所有天数的总和，包括date注意dateLast ：dateLast不计入结帐日期。也许这对于 SQL 查询来说太复杂了，应该在 PHP 中作为一系列子例程处理？

标签： mysqlsql

解决方案

If you don't need to split the number of days of one reservation over multiple months, then you can just use 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

If you want to split them, then I hope your version (MySQL 8+ or MariaDB 10.2+) supports recursive queries. In that case you can expand the date range to one row per day in the range and count them:

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

推荐阅读