mysql 根据起止年份 查询所选年份数据

1、时间字段是datetime类型

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

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

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

4、sql

select
    bri.orgid,
    date_format(years.date, '%Y') 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('2014-01-01', numlist.id) as 'date', 
        numlist.id 
    from ( select n100.i * 365 as id from num as n100 ) as numlist 
    where adddate('2014-01-01', numlist.id) <= '2019-12-31' ) years
    
left join building_resident_info bri on year (years.date) = year (bri.entrytime) and bri.status='1'

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

 

5、效果图

 

　　

 

如有疏漏，请指正！谢谢。

转载于:https://www.cnblogs.com/w-yu-chen/p/10259395.html