mysql 查询当前年的所有月份
数据统计中经常有这样的需求,统计今年1-12月的数据,以下博主搜寻了2种方法，希望可以帮到大家
第一种:采用union方式
select concat((select year(now())), ‘-01’) as month
union
select concat((select year(now())), ‘-02’)
union
select concat((select year(now())), ‘-03’)
union
select concat((select year(now())), ‘-04’)
union
select concat((select year(now())), ‘-05’)
union
select concat((select year(now())), ‘-06’)
union
select concat((select year(now())), ‘-07’)
union
select concat((select year(now())), ‘-08’)
union
select concat((select year(now())), ‘-09’)
union
select concat((select year(now())), ‘-10’)
union
select concat((select year(now())), ‘-11’)
union
select concat((select year(now())), ‘-12’)
第二种:先建立一个1-12的数组,拿取当前每个月的第一天,之后可以按照需求拿取月份和日期.
select adddate(DATE_SUB(CURDATE(), INTERVAL dayofyear(now()) – 1 DAY),
INTERVAL numlist.id – 1 month) as ‘date’
from (SELECT @xi := @xi + 1 as id
from (SELECT 1 UNION SELECT 2 UNION SELECT 3) xc1,
(SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) xc2,
(SELECT @xi := 0) xc0) as numlist
补充一下 mysql date_format 用法：
DATE_FORMAT(‘2000-05-07 05:06:07’, ‘%Y-%m-%d %H:%i:%s’) – 2000-05-07 05:06:07
