使用 mysql 统计日期范围的数据条数

合集 - 报错(63)

1.MySQL 8.0 java.sql.SQLException Unknown system variable 'query_cache_size'2021-06-092.yarn : 无法将“yarn”项识别为 cmdlet、函数、脚本文件或可运行程序的名称2022-03-133.idea中文乱码解决四种方法2024-01-194.elasticsearch安装问题记录2022-04-175.Swagger之Failed to start bean ‘documentationPluginsBootstrapper‘； nested exception is java.lang2022-03-096.switchHosts应用时，没有写入 Hosts 文件的权限2022-01-247.IDEA 控制台中文乱码2024-01-198.java.lang.IllegalArgumentException: Base64-encoded key bytes may only be specified for HMAC2023-12-289.git之kex_exchange_identification: Connection closed by remote host2023-12-1410.Oracle之ORA-29275: 部分多字节字符2023-12-0211.maven之‘settings.xml‘ has syntax errors2023-12-0212.vue项目本地映射到外网访问时出现 Invalid Host header2023-11-1913.vue之Error: Unknown option: .devServer.2023-11-1914.Nacos之Injection of @DubboReference dependencies is failed；2023-11-1515.redis之org.springframework.data.redis.RedisSystemException: Error in execution2023-11-1316.reduce the indexing time and cpu load with pre-build jdk shared indexes2023-09-2317.docker安装xxl-job连接数据库时显示无法连接问题2023-09-1118.解决时间戳问题2023-06-05

19.使用 mysql 统计日期范围的数据条数2023-06-04

20.error An unexpected error occurred: https://registry.npmmirror.com2023-06-0221.Visual Studio 2022 2022 不支持 .NET Framework 4.0项目解决办法2023-05-2722.webpack的使用2023-05-2723.node.js运行项目时提示digital envelope routines::unsupported2023-05-2724.Typora图片路径问题之相对路径前没有点斜杠2023-03-1125.Personal access tokens (classic)与Fine-grained personal access tokens Beta区别2023-02-1626.GitHub Action: The process ‘/usr/bin/git‘ failed with exit code 128 解决方案2023-02-1627.Android Studio 中模拟器无法打开，提示Error launching emulator2022-11-0228.AndroidStudio中Android模拟器没网解决办法2022-11-0229.C#中System.ArgumentOutOfRangeException:“提供的行索引超出范围。2022-09-0930.VisualStudio运行程序，点击应用程序时，弹出多个个窗体问题2022-09-0931.C#中无法将文件“obj\x86\Debug\xxx.exe”复制到“bin\Debug\xxx.exe”。文件“bin\Debug\xxx.exe”正由另一进程使用，因此该进程无法访问此文件.2022-09-0932.ssm之Error creating bean with name ‘bookServiceImpl‘: Injection of resource dependencies failed；2022-08-0833.Node.js之npm ERR code EPERM npm ERR syscall open npm ERR2022-05-3134.Cookie报错: An invalid character [32] was present in the Cookie value2022-05-3135.Android之Installed Build Tools revision 32.0.0 is corrupted.2022-05-0336.安装node.js需要注意的问题2022-04-1737.RabbitMQ-错误：Failed to download metadata for repo ‘base‘: Cannot download repomd.xml2022-04-1738.centos8.2安装rabbitMQ遇到的问题2022-04-1739.‘Starting Tomcat v8.5 Server at localhost‘ has encountered a problem.2022-04-0240.Eclipse运行报Could not launch external web browser for ..2022-03-2641.docker之Error response from daemon: driver failed programming external connectivity on endpoint2022-03-1742.VisualStudioCode终端出现-＞在此系统上禁止运行脚本2022-03-1243.java.lang.IllegalStateException: Failed to register dubbo://139.224.33.130:208812022-02-2544.springCloud之Cloud not autowire.No beans of ‘‘ type found2022-02-2245.springCloud之This is deprecated. The new driver class is `com.mysql.cj.jdbc.Driver‘.2022-02-2246.Spring Cloud Config 客户端报错：No spring.config.import property has been defined2022-02-2247.rg.springframework.beans.factory.UnsatisfiedDependencyException: Error creating bean with name2022-01-2648.org.springframework.beans.factory.BeanCreationException: Error creating bean with name ‘‘2022-01-2649.SpringBoot之Invalid bound statement (not found): com.nxg.mapper.UserMapper.selectUserByPage2022-01-1250.SpringBoot之Failed to load ApplicationContext2022-01-1051.Maven之导入thymeleaf依赖飘红问题2022-01-0952.SpringBoot之Establishing SSL connection without server‘s identity verification is not recommended.2022-01-0953.SpringBoot之 Failed to load ApplicationContext2022-01-0954.Eclipse报错-Description Resource Path Location Type Unbound classpath container: ‘JRE System Library2021-10-1855.sqlServer2019附加出现的问题整理2021-09-2356.ssm中的中Failed to load resource: the server responded with a status of 404 ()2021-08-3157.Maven之IDEA创建XML Configuration File时没有Spring Config的选项2021-08-0458.MVC之Error:java: 不再支持源选项 5。请使用 6 或更高版本。2021-08-0459.Maven之Failed to execute goal org.apache.maven.plugins:maven-install-plugin:2.4:install (default-cli)2021-07-2660.在安装Maven检查版本时出现Please set the JAVA_HOME variable in your environment to match the locat2021-07-2561.在linux下 wget 下载报错 http request sent,awaiting response... 404 not found2021-07-2462.异常之java.lang.nullpointerexception 怎么解决及出现原因？2021-07-1663.JavaWeb之MVC---数据库连接问题2021-06-10

收起

需求：

查询2019-08到2019-09所有数据

遇到的问题

[Err] 1055 - Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'session1.s1.Date' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

原因：

这似乎与数据库更改本身无关，但在8.0.16版本中，关于DATETIME和TIMESTAMP类型与strings的比较发生了一些变化。该行为已在8.0.16更改日志中记录如下：
当DATE值与常量字符串进行比较时，MySQL首先尝试将字符串转换为DATE，然后执行比较。当转换失败时，MySQL将DATE作为字符串执行比较，这可能导致不可预测的行为。现在在这种情况下，如果字符串转换为DATE失败，ER_WRONG_VALUE比较失败。（错误#29025656）
此外，这被报告为bug here。
PD：你的查询在我的旧版MySQL 5.7上工作。

解决方法

方式一：

select 
CONCAT(YEAR(s1.Date),'-',RIGHT(MONTH(s1.Date)+100,2)) as Month,
SUM(f1.Payment) as TicketsRevenue,
COUNT(DISTINCT s1.ScheduleId) as FlightsAmount,
COUNT(f1.ReservationId) as TicketsAmount
FROM `schedule` as s1
left join flightreservation as f1 ON s1.ScheduleId = f1.ScheduleId
WHERE (s1.Date > '2019-08-01' and s1.Date < '2019-09-30') and s1.Status` = 'Confirmed'
GROUP BY YEAR(s1.Date),MONTH(s1.Date)

缺点：只能在mysql5.7中使用

CONCAT(YEAR(s1.Date),'-',RIGHT(MONTH(s1.Date)+100,2)) as Month 作用：

1. YEAR(s1.Date)：这部分代码返回字段 s1.Date 的年份部分。YEAR() 是一个SQL函数，用于提取日期> 字段的年份。
2. MONTH(s1.Date)+100：这部分代码返回字段 s1.Date 的月份部分，并加上 100。这是为了确保即使
   月份是一位数，也能保留两位数的格式。例如，如果月份是 5，加上 100 后得到 105。
3. RIGHT(MONTH(s1.Date)+100,2)：这部分代码返回上一步结果的最右两位数，即提取月份的后两位 > 数。RIGHT() 是一个SQL函数，用于返回字符串的最右边的指定位数。
4. CONCAT(YEAR(s1.Date),‘-’,RIGHT(MONTH(s1.Date)+100,2))：这部分代码将年份和月份拼接起 > 来， 中间用短横线分隔。CONCAT() 是一个SQL函数，用于将多个字符串拼接成一个字符串。

综合起来，这段代码的作用是将日期字段 s1.Date 的年份和月份拼接成一个表示月份的字符串，格式 > 为 YYYY-MM。例如，如果 s1.Date 的值是 2019-08-15，那么 Month 字段的值将是 2019-05。

方式二（推荐）：

SELECT 
  DATE_FORMAT(s1.Date, '%Y-%m') as Month,
  SUM(f1.Payment) as TicketsRevenue,
  COUNT(DISTINCT s1.ScheduleId) as FlightsAmount,
  COUNT(f1.ReservationId) as TicketsAmount
FROM `schedule` as s1
LEFT JOIN flightreservation as f1 ON s1.ScheduleId = f1.ScheduleId
WHERE (s1.Date BETWEEN '2019-08-01' AND  LAST_DAY('2019-09-01')) AND s1.Status = 'Confirmed'
GROUP BY DATE_FORMAT(s1.Date, '%Y-%m')

其中
DATE_FORMAT(s1.Date, '%Y-%m') as Month 表示查询年份-月份
DATE_FORMAT函数作用：获取月份和年份的组合字符串
LAST_DAY函数表示返回当前 date 或 datetime 类型值对应月份的最后一天。

注意：上面的Date都是表中的字段

方式三：

SELECT
DATE_FORMAT(s1.Date, '%Y-%m') as Month,
SUM(f1.Payment) as TicketsRevenue,
COUNT(DISTINCT s1.ScheduleId) as FlightsAmount,
COUNT(f1.ReservationId) as TicketsAmount
FROM
schedule as s1
LEFT JOIN flightreservation as f1 ON s1.ScheduleId = f1.ScheduleId
WHERE
(s1.Date > '2019-08-01'
AND s1.Date < '2019-09-30')
AND s1.Status = 'Confirmed'
GROUP BY
DATE_FORMAT(s1.Date, '%Y-%m')

扩展

统计当月所遇数据条数：

SELECT * FROM schedule WHERE `date` like '2022-03%'

这里为了便于区别就使用 date字段（表中）

附件

参考1：https://www.sunzhongwei.com/mysql-query-date-type-fields-in-a-month
参考2：MySQL8.0中的日期类数据及其函数
参考3：mysql SQL错误1525 HY000：DATETIME值不正确：