MySQL查询12个月数据，无数据补0

2018-03-08 本文已影响0人打不死的小强8号

1.数据库和表就用自己的，下面是代码

select

case month(shi_com_time) when '1' then sum(mileage) else 0 end as 一月份,

case month(shi_com_time) when '2' then sum(mileage) else 0 end as 二月份,

case month(shi_com_time) when '3' then sum(mileage) else 0 end as 三月份,

case month(shi_com_time) when '4' then sum(mileage) else 0 end as 四月份,

case month(shi_com_time) when '5' then sum(mileage) else 0 end as 五月份,

case month(shi_com_time) when '6' then sum(mileage) else 0 end as 六月份,

case month(shi_com_time) when '7' then sum(mileage) else 0 end as 七月份,

case month(shi_com_time) when '8' then sum(mileage) else 0 end as 八月份,

case month(shi_com_time) when '9' then sum(mileage) else 0 end as 九月份,

case month(shi_com_time) when '10' then sum(mileage) else 0 end as 十月份,

case month(shi_com_time) when '11' then sum(mileage) else 0 end as 十一月份,

case month(shi_com_time) when '12' then sum(mileage) else 0 end as 十二月份

from bu_waybill

where year(shi_com_time)='2018';

二、执行结果，希望能帮到你

2018-03-08 xiaomage