MySQL 根据某一年 查询12个月的数据

1、时间字段是datetime类型

2、需要用到中间表,表结构和数据链接下载

链接:https://pan.baidu.com/s/1tAFU4nm1p4CkSneXwceA_A
提取码:b3xi

3、前两个 adddate 月日需要在代码中写死 从每年的-01-01开始,-最后一个日期同理xxxx-12-31

4、下面是sql

select
    concat(month(years.date),'') as year,
    years.date,
    date_format(years.date, '%Y-%m') AS years,
    sum( case when bri.status = '1' then 1 else 0 end ) as num, -- 总数
    sum( case when bri.status = '1' and bri.sex='1' then 1 else 0 end  ) as numman, -- 男性数量
    sum( case when bri.status = '1' and bri.sex='2' then 1 else 0 end  ) as numgrid -- 女性数量
    
from
    ( select adddate('2018-01-01', INTERVAL numlist.id month) as 'date', numlist.id 
    from ( select n100.i * 1 as id from num as n100 ) as numlist 
    where adddate('2018-01-01', INTERVAL numlist.id month) <= '2018-12-31' ) years
    
left join building_resident_info bri on date_format(years.date, '%Y-%m')  = date_format(bri.entrytime, '%Y-%m') and bri.status='1'

and bri. status = '1'
and bri.orgid like concat('37.02.82', '%')
group by years.date

5、效果图如下

 

 

如有疏漏,请指正!谢谢。

posted @ 2019-01-12 13:04  雁书几封  阅读(16100)  评论(2编辑  收藏  举报