【mysql 查询一年中的所有月份】

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