mysql如果空显示_mysql按照自己定义的值显示如果为空的就显示0

题目：一张表：

CREATE TABLE `tbl_time` (

`id` int(10) unsigned NOT NULL AUTO_INCREMENT,

`time` char(10) NOT NULL DEFAULT '',

PRIMARY KEY (`id`)

) ENGINE=InnoDB;

mysql> select * from tbl_time;

+----+------------+

| id | time       |

+----+------------+

|  1 | 2015-10-27 |

|  2 | 2015-10-27 |

+----+------------+

但是我期望的结果是

2015-10-27，2015-10-28，2015-10-29，2015-10-30的2,0,0,0

而且只能用sql 语句

解决：

mysql> select a.tmp,if(time is null,0,count(*))as ant from (select '2015-10-27' as tmp union select '2015-10-28' union select '2015-10-29' union select '2015-10-30') a left join tbl_time on a.tmp=tbl_time.time group by a.tmp;

+------------+-----+

| tmp        | ant |

+------------+-----+

| 2015-10-27 |   2 |

| 2015-10-28 |   0 |

| 2015-10-29 |   0 |

| 2015-10-30 |   0 |

+------------+-----+