sql left join count 左表为空表的时候出现空行
sql left join count 左表为空表的时候出现空行
mysql> CREATE TABLE `test`.`tab1` (
`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`name` VARCHAR( 20 ) NOT NULL ,
`del` INT NOT NULL
) ENGINE = MYISAM ;
mysql> CREATE TABLE `test`.`tab2` (
`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`tid` INT NOT NULL ,
`val` INT NOT NULL ,
`del` INT NOT NULL
) ENGINE = MYISAM ;
mysql> select tab1.*, count(tab2.val) from tab1 left join tab2 on tab2.tid = tab1.id;
+----+------+-----+-----------------+
| id | name | del | count(tab2.val) |
+----+------+-----+-----------------+
| NULL | NULL | NULL | 0 |
+----+------+-----+-----------------+
1 row in set (0.00 sec)
哎,有一个空行,怎么办啊,count返回了个0,难道别人的系统里面,左表为空的时候都会显示出来一行吗。我不相信,最后想到了办法。哈哈。。。
加 having id is not null
mysql> select tab1.*, count(tab2.val) from tab1 left join tab2 on tab2.tid = tab1.id having id is not null;
Empty set (0.01 sec)
搞定。呵呵。经验分享给大家,希望更多人受用。