mysql中查询当年1-12月数据_mysql查询1到12月份数据

数据统计中经常有这样的需求,统计今年1-12月的贸易额,统计了两种比较使用的方法;

第一种:采用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