SELECT x.YEAR,IFNULL(b.ecaConsum,0) from (SELECT '2016' AS YEAR UNION select DATE_FORMAT( ADDDATE(from_unixtime(unix_timestamp(CONCAT('2016','-01-01'))), INTERVAL - 1 YEAR ), '%Y ' ) UNION select DATE_FORMAT( ADDDATE(from_unixtime(unix_timestamp(CONCAT('2016','-01-01'))), INTERVAL - 2 YEAR ), '%Y ' ) UNION select DATE_FORMAT( ADDDATE(from_unixtime(unix_timestamp(CONCAT('2016','-01-01'))), INTERVAL - 3 YEAR ), '%Y ' ) UNION select DATE_FORMAT( ADDDATE(from_unixtime(unix_timestamp(CONCAT('2016','-01-01'))), INTERVAL - 4 YEAR ), '%Y ' )) x LEFT JOIN ( SELECT DATE_FORMAT( date, "%Y" ) as time,sum(elect_cons) as ecaConsum,date from eca_power_consum where user_id='13231312' GROUP BY DATE_FORMAT( date, "%Y" ) ) b on x.YEAR=b.time
