今天看见一公司的数据库面试题，其中有道一开始没想起怎么做，后来摸索了下终于做出来了。
题目是：两个表联合查询，当表2的记录在表A里没有时，将其设置为0.
mysql> select * from t1;
+----+------+
| id | name |
+----+------+
| 1 | bbs |
| 2 | bb |
| 3 | cc |
+----+------+
3 rows in set (0.01 sec)
mysql> select * from t2;
+----+------+
| id | name |
+----+------+
| 1 | aa |
| 2 | bb |
+----+------+
2 rows in set (0.00 sec)
mysql> select as name1, as name2 from t1 left join t2 on =;
+-------+-------+
| name1 | name2 |
+-------+-------+
| bbs | aa |
| bb | bb |
| cc | NULL |
+-------+-------+
3 rows in set (0.00 sec)
将NULL变为0
第一种写法：
mysql> select as name1,if( is NULL,0,) as name2 from t1 left join t2 on =;
+-------+-------+
| name1 | name2 |
+-------+-------+
| bbs | aa |
| bb | bb |
| cc | 0 |
+-------+-------+
3 rows in set (0.00 sec)
第二种写法：
mysql> select as name1,(case when is NULL then '0' else end) as name2 from t1 left join t2 on =;
+-------+-------+
| name1 | name2 |
+-------+-------+
| bbs | aa |
| bb | bb |
| cc | 0 |
+-------+-------+
3 rows in set (0.00 sec)
参考：
http:///share/detail/22393623
