MySql按月份查询数据,无则值显示0

#查询当年数据,按月份查询,无则显示0

 select concat(h2.month,'月') as 'name',ifnull(normal_month_count,0) as 'value','更新数据量' as 'series' 
 from hp_table_count_month h2 
 left join ( SELECT T.*,MONTH(date_value)+0 M FROM ( select normal_month_count,date_value from hp_table_count 
 where year(date_value)=date_format(SYSDATE(),'%Y') ) T) t on t.m = h2.month order by h2.id

辅助表
hp_table_count_month

– 参考把全数据作为主表,要查的数据全部带条件查出作为附表(不可直接当附表,一定带全部条件)
– 今年的所有数据为T,
MONTH(date_value)+0 M 为今年数据里面对应的月份,
t 为数据和其月份, 附表
h2 作为查出所有月份 ,主表

衍生例子,按某个分类来查
例1.按分类查询数量

select d.dict_label as 'name',ifnull(h.value,0) as 'value'  from
(select dict_value as dict_value,dict_label as dict_label ,dict_type as dict_type from sys_dict_data where dict_type= 'high_tech_realm_one')d
left join (select count(1) as 'value',field_one from hp_high_tech where year = (select max(year) from hp_high_tech ) group by field_one ) h
on d.dict_value=h.field_one and d.dict_type= 'high_tech_realm_one'

例2

select d.dict_label as 'name',ifnull(h.value,0) as 'value'  from
(select dict_value as dict_value,dict_label as dict_label ,dict_type as dict_type from sys_dict_data where dict_type= 'high_tech_realm_one')d
left join (select ifnull(SUM(revenue_annual),0)  as 'value',field_one from hp_high_tech where year = (select max(year) from hp_high_tech ) group by field_one ) h
on d.dict_value=h.field_one and d.dict_type= 'high_tech_realm_one'