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

2019独角兽企业重金招聘Python工程师标准>>>

题目：一张表：

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 | 

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

转载于:https://my.oschina.net/u/242853/blog/523247