MySQL中通过SQL来获得所有当年年月的解决办法

最新推荐文章于 2024-04-04 12:20:46 发布
原创 最新推荐文章于 2024-04-04 12:20:46 发布 · 1.1w 阅读 · 3 ·
CC 4.0 BY-SA版权
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
文章标签：

#mssql #sql

本文介绍了一种通过SQL代码直接获取当前日期的年月部分的方法，适用于仅需使用年月而不关注具体日期和时间的场景。该方法通过特定的SQL语句实现，能有效满足项目中对日期数据的特定需求。

 

      我们在做项目的时候，很多时候我们只需要使用当前一年内的年月，而不用日期的具体的时间，目前很多提供的控件，可能要么可以直接获取年月日和具体的时间，要么就是直接获取年月日，那么很多时候不适合我们的需求，不过我们可以通过编程的方法来解决，比如我们通过程序的方式来截取时间，截到月份即可，也能符合我们的需求，但是今天想提供另外一个方法，直接通过SQL来获取日期的年月。

通过下面的SQL代码，只需要把tableName换成自己数据库里面随便一个大于12行的表格即可。

通过如下的方式：

select case when length(mon)=1 then concat(left(CURRENT_DATE,5),'0',mon) else concat(left(CURRENT_DATE,5),mon) end months from (select @m:=@m+1 mon from tableName,(select @m:=0) a ) aa limit 12;

 

结果如下图所示：

说明：

这里面的TableName必须要求数据量在12行以上，才可以。

【无标题】MySQL中获取年月日相同的方法
shz_123的博客
11-23 2395
这是一个时间的数据表(time) 1.获取当天的inputTime的全部数据（两种方法） select inputTime from time where DATE_FORMAT(inputTime,'%d')=DATE_FORMAT(now(),'%d'); 或 select inputTime from time where TO_DAYS(inputTime)=TO_DAYS(now()); 2.获取当月的outputTime的全部数据 select outputTime from ...
sql server 查询本年的每个月的数据
qq_42988813的博客
02-20 1481
sql server 查询本年的每个月的数据 一、以一行数据的形式，显示本年的12月的数据，本示例以2017年为例，根据CreateDate字段判断，计算总和，查询语句如下： select sum(case when datepart(month,CreateDate)=1 then 1 else 0 end) as '1月', sum(case whe...
查询当年所有月份
SSD1397713787的博客
05-26 509
SqlServer sql获取当年所有月份显示
【mysql 查询一年中的所有月份】
weixin_43163986的博客
07-06 4480
数据统计中经常有这样的需求,统计今年1-12月的数据,以下博主搜寻了2种方法，希望可以帮到大家。第二种:先建立一个1-12的数组,拿取当前每个月的第一天,之后可以按照需求拿取月份和日期.第一种:采用union方式。
SQLServer获取本年所有月份，本月所有天数
绣花针
04-15 7198
1.本年所有月份 --本年所有的月数 with t as ( select replace(convert(varchar(7),dateadd(mm,t.number,dateadd(year, datediff(year, 0, getdate()), 0)),120),'-','') month from (select number from master..spt_values wh...
sql server取某个时间段内所有日期或者所有月份
altzlo7909的博客
11-04 264
取所有月份： declare @begin datetime,@end datetime set @begin='2015-2-6' set @end='2015-12-2' declare @months int set @months=DATEDIFF(month,@begin,@end) select convert(varchar(7),DATEAD...
mysql只取年月日的SQL写法.sql
02-23
mysql只取年月日的SQL写法
MYSQL替换时间(年月日)字段时分秒不变实例解析
12-15
总结来说，要替换MySQL中的时间字段的年月日部分而不改变时分秒，可以使用`REPLACE`、`ADDTIME`+`INTERVAL`或`CONCAT`+`DATE_FORMAT`等方法。同时，注意避免在`UPDATE`语句中直接引用要更新的表作为子查询的目标，...
sqlserver/mysql按天、按小时、按分钟统计连续时间段数据【推荐】
09-09
在SQL Server和MySQL数据库中，按天、按小时或按分钟统计连续时间段数据是一项常见的需求，这有助于分析数据峰值和趋势。以下是如何实现这一目标的详细步骤和知识点： 1. **master..spt_values表**： - `master.....
mysql 获取当年的所有年月
往事随风的博客
11-28 4740
SELECT CASE WHEN length(mon) = 1 THEN concat( LEFT (CURRENT_DATE, 5), '0', mon ) ELSE concat(LEFT(CURRENT_DATE, 5), mon) END months FROM ( SELECT @m :=@m + 1 mon FROM ais_contrac...
SQL获取时间段内的所有月份
weixin_30337251的博客
12-23 300
select convert(varchar(7),dateadd(month,number,'2010-01-01'),120) AS MONTHfrom master..spt_valueswhere type='P' and dateadd(month,number,'2010-01-01')<='2010-09-01' 转载于:https://www.cnblogs.com/xiuf...
SQL查询本年每月的数据
zgscwxd的博客
09-03 3817
SQL查询本年每月的数据
MySQL获取当前年份月份日期
热门推荐
qq_28315923的博客
06-11 3万+
MySQL获取当前年份月份日期 – 获取年份 YEAR(CURDATE())； 2019 – 获取月份 MONTH(CURDATE())； 6 – 当前时间 DATE(CURDATE())； 2019-06-11 – 获取年月 date_format(CURDATE(), ‘%Y%m’) 201906 ...
SQL Server中获取指定时间段内的所有月份
weixin_30549657的博客
01-24 510
例如查询 2012-1-5 到 2012-11-3 之间所有的月份 declare@begindatetime,@enddatetime set@begin='2012-1-5' set@end='2012-11-3' declare@monthsint set@months=DATEDIFF(month,@begin,@end) selectco...
SQL按年月日查询
weixin_42418369的博客
10-26 2620
1、统计某一年十二个月份的数据（在多个表中）1、统计本年且按某一类型统计数据。2、查询本月数据或者某月的数据。3、本年或者某一年的数据。3、查询上个月的数据。
【SQL】SQL查询去年同期
qq_51350969的博客
11-08 5055
your_table。
MySQL时间查询讲解+实战教学（查询本月、上个月、下个月等等的数据）
我认不到你的博客
12-26 7131
查询 年-月-日 时:分:秒 查询 年-月-日 查询 年-月 查询当前年 查询当前月 查询当前日 查询当前星期几 查询当前季度 查询 时:分:秒 万能公式
mysql 日期查询当天，当月，上个月，当年的数据sql语句
2301_76348344的博客
04-04 2518
在这个部分总结了2019年到目前为止Java常见面试问题，取其面试核心编写成这份文档笔记，从中分析面试官的心理，摸清面试官的“套路”，可以说搞定90%以上的Java中高级面试没一点难度。本节总结的内容涵盖了：消息队列、Redis缓存、分库分表、读写分离、设计高并发系统、分布式系统、高可用系统、SpringCloud微服务架构等一系列互联网主流高级技术的知识点。（上述只是一个整体目录大纲，每个点里面都有如下所示的详细内容，从面试问题——分析面试官心理——剖析面试题——完美解答的一个过程）
SQL查询一年的十二个月份，形成报表
极客神殿
07-02 2万+
SELECT YEAR(日期字段) 年度,SUM(CASE WHEN MONTH(日期字段) =1 THEN 统计的字段 ELSE 0 END) 一月, SUM(CASE WHEN MONTH(日期字段) =2 THEN 统计的字段 ELSE 0 END) 二月, SUM(CASE WHEN MONTH(日期字段) =3 THEN 统计的字段 ELSE 0 END) 三月, SUM(CASE...
MySQL中根据一个年月日格式的字段按照月份进行分区，SQL怎么写
最新发布
09-11
<think> 我们已经在之前的回答中详细说明了MySQL根据日期字段按月自动分区的实现方法。现在用户再次询问这个问题，可能是需要更简洁的总结或补充说明。根据引用内容，我们需要注意： 1. 查询条件中直接使用日期字段而不使用函数（如YEAR()），否则会导致分区失效（引用[1]） 2. 分区策略选择：按月分区比按年月联合分区在特定场景下更优（引用[3]） 因此，我们将提供一个简洁的按月分区创建示例，并强调关键注意事项。 ### MySQL根据日期字段按月分区的SQL写法 #### 1. 创建分区表（按月分区） 假设表名为`sales_data`，日期字段为`sale_date`（DATE类型）： ```sql CREATE TABLE sales_data ( id INT NOT NULL AUTO_INCREMENT, product_name VARCHAR(100), sale_date DATE NOT NULL, -- 必须为DATE/DATETIME类型 amount DECIMAL(10,2), PRIMARY KEY (id, sale_date) -- 分区字段必须包含在主键中 ) PARTITION BY RANGE (TO_DAYS(sale_date)) ( PARTITION p202301 VALUES LESS THAN (TO_DAYS('2023-02-01')), -- 2023年1月 PARTITION p202302 VALUES LESS THAN (TO_DAYS('2023-03-01')), -- 2023年2月 PARTITION p_max VALUES LESS THAN MAXVALUE -- 兜底分区（必须） ); ``` #### 2. 关键说明 - **分区原理**：使用`TO_DAYS()`将日期转为整数天数进行范围划分 - **分区边界**：每个分区包含整月数据，边界值为下个月1号（如1月分区边界为`2023-02-01`） - **查询优化**：WHERE条件中**直接使用日期字段**才能触发分区裁剪： ```sql -- 正确示例（只扫描2023年1月分区） SELECT * FROM sales_data WHERE sale_date BETWEEN '2023-01-01' AND '2023-01-31'; -- 错误示例（扫描所有分区） SELECT * FROM sales_data WHERE YEAR(sale_date) = 2023 AND MONTH(sale_date) = 1; ``` #### 3. 自动维护分区（补充） 通过事件每月自动添加新分区： ```sql -- 存储过程：自动添加下个月分区 DELIMITER $$ CREATE PROCEDURE add_sales_partition() BEGIN SET @next_month = DATE_FORMAT(CURRENT_DATE + INTERVAL 1 MONTH, '%Y-%m-01'); SET @partition_name = CONCAT('p', DATE_FORMAT(@next_month, '%Y%m')); SET @sql = CONCAT( 'ALTER TABLE sales_data REORGANIZE PARTITION p_max INTO (', 'PARTITION ', @partition_name, ' VALUES LESS THAN (TO_DAYS(\'', @next_month, '\')),', 'PARTITION p_max VALUES LESS THAN MAXVALUE)' ); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; END$$ DELIMITER ; -- 每月1号执行的事件 CREATE EVENT auto_add_sales_partition ON SCHEDULE EVERY 1 MONTH STARTS DATE_FORMAT(CURRENT_DATE + INTERVAL 1 MONTH, '%Y-%m-01 02:00:00') ON COMPLETION PRESERVE ENABLE DO CALL add_sales_partition(); ``` #### 4. 注意事项 1. **日期格式验证** 确保数据中的日期格式合法，非法日期会导致插入失败（如`2023-02-30`） 2. **时区影响** 事件调度器使用系统时区，需确保与应用时区一致： ```sql SET GLOBAL time_zone = '+8:00'; -- 东八区 ``` 3. **分区数量上限** MySQL单表最多支持8192个分区（不同版本有差异），建议定期清理旧分区： ```sql -- 删除2023年1月分区 ALTER TABLE sales_data DROP PARTITION p202301; ``` 4. **性能监控** 通过EXPLAIN验证分区裁剪是否生效： ```sql EXPLAIN PARTITIONS SELECT * FROM sales_data WHERE sale_date = '2023-01-15'; ``` ### 相关问题 1. 如何将现有表转换为按月分区表？ 2. 分区后如何高效清理历史数据（如删除3年前数据）？ 3. MySQL分区表有哪些限制？ 4. 如何优化跨分区查询的性能？ </think> ### MySQL根据日期字段按月分区的SQL写法 #### 1. 基础分区表创建（按月分区） ```sql CREATE TABLE your_table ( id INT NOT NULL AUTO_INCREMENT, data_content VARCHAR(255), date_field DATE NOT NULL, -- 日期字段（必须是DATE/DATETIME类型） PRIMARY KEY (id, date_field) -- 分区字段必须包含在主键中 ) PARTITION BY RANGE (TO_DAYS(date_field)) ( PARTITION p202301 VALUES LESS THAN (TO_DAYS('2023-02-01')), -- 2023年1月分区 PARTITION p202302 VALUES LESS THAN (TO_DAYS('2023-03-01')), -- 2023年2月分区 PARTITION p_max VALUES LESS THAN MAXVALUE -- 必须包含的兜底分区 ); ``` #### 2. 关键要点说明 1. **日期字段要求** - 字段类型必须是 `DATE` 或 `DATETIME` - 如果是字符串类型需转换： ```sql ALTER TABLE your_table MODIFY date_field DATE; ``` 2. **分区函数选择** - 使用 `TO_DAYS()` 将日期转为整数天数 $$ \text{TO\_DAYS('2023-01-01') = 738885} $$ - `YEAR()`/`MONTH()` 函数会导致分区裁剪失效[^1] 3. **分区边界定义** - 分区包含规则：左闭右开区间 $$ \text{值} < \text{边界值} $$ - 例：`VALUES LESS THAN (TO_DAYS('2023-02-01'))` 包含整个2023年1月的数据 #### 3. 自动维护分区方案 ```sql -- 创建自动添加分区的存储过程 DELIMITER $$ CREATE PROCEDURE auto_add_month_partition() BEGIN DECLARE next_month DATE DEFAULT DATE_FORMAT(CURRENT_DATE + INTERVAL 1 MONTH, '%Y-%m-01'); DECLARE part_name VARCHAR(20) DEFAULT CONCAT('p', DATE_FORMAT(next_month, '%Y%m')); SET @sql = CONCAT( 'ALTER TABLE your_table REORGANIZE PARTITION p_max INTO (', 'PARTITION ', part_name, ' VALUES LESS THAN (TO_DAYS(\'', next_month, '\')),', 'PARTITION p_max VALUES LESS THAN MAXVALUE)' ); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; END$$ DELIMITER ; -- 创建每月执行的事件 CREATE EVENT auto_partition_event ON SCHEDULE EVERY 1 MONTH STARTS DATE_FORMAT(CURRENT_DATE + INTERVAL 1 MONTH, '%Y-%m-01 01:00:00') ON COMPLETION PRESERVE DO CALL auto_add_month_partition(); -- 启用事件调度器 SET GLOBAL event_scheduler = ON; ``` #### 4. 查询优化建议 1. **正确查询写法**（触发分区裁剪） ```sql -- 正确：直接使用日期字段 SELECT * FROM your_table WHERE date_field BETWEEN '2023-01-01' AND '2023-01-31'; -- 错误：使用函数导致全表扫描[^1] SELECT * FROM your_table WHERE YEAR(date_field) = 2023 AND MONTH(date_field) = 1; ``` 2. **验证分区裁剪** ```sql EXPLAIN PARTITIONS SELECT * FROM your_table WHERE date_field = '2023-01-15'; ``` #### 5. 分区管理操作 ```sql -- 查看所有分区 SELECT partition_name, partition_description FROM information_schema.partitions WHERE table_name = 'your_table'; -- 删除旧分区（如删除2023年1月） ALTER TABLE your_table DROP PARTITION p202301; -- 手动添加分区（可选） CALL auto_add_month_partition(); ``` ### 注意事项 1. **数据类型验证** 确保所有 `date_field` 值都是有效日期，非法值（如 `2023-02-30`）会导致插入失败 2. **时区一致性** 事件调度器使用系统时区，确保与应用时区匹配： ```sql SET GLOBAL time_zone = '+8:00'; -- 设为东八区 ``` 3. **分区数量限制** MySQL 8.0 单表最多支持8192个分区，定期清理旧分区： ```sql -- 保留最近12个月分区 ALTER TABLE your_table DROP PARTITION p202201; ``` 4. **性能影响** $$ \text{插入性能} \propto \frac{1}{\sqrt{\text{分区数量}}} $$ 建议分区数保持在百级别以内 --- ### 相关问题 1. 如何将现有非分区表转换为按月分区表？ 2. 分区表在哪些场景下性能反而会下降？ 3. 如何高效清理分区表中的历史数据？ 4. MySQL分区和分表方案应该如何选择？ 5. 分区字段不在主键中会导致什么问题？
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值