sql left join count 左表为空表的时候出现空行

2010-04-30 09:45  1437人阅读  评论 (0)
Tags: mysql

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)

搞定。呵呵。经验分享给大家,希望更多人受用。