今天看见一公司的数据库面试题，其中有道一开始没想起怎么做，后来摸索了下终于做出来了。
题目是：两个表联合查询，当表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 t1.name as name1,t2.name as name2 from t1 left join t2 on t1.id=t2.id;
+-------+-------+
| name1 | name2 |
+-------+-------+
| bbs | aa |
| bb | bb |
| cc | NULL |
+-------+-------+
3 rows in set (0.00 sec)
将NULL变为0
第一种写法：
mysql> select t1.name as name1,if(t2.name is NULL,0,t2.name) as name2 from t1 left join t2 on t1.id=t2.id;
+-------+-------+
| name1 | name2 |
+-------+-------+
| bbs | aa |
| bb | bb |
| cc | 0 |
+-------+-------+
3 rows in set (0.00 sec)
第二种写法：
mysql> select t1.name as name1,(case when t2.name is NULL then '0' else t2.name end) as name2 from t1 left join t2 on t1.id=t2.id;
+-------+-------+
| name1 | name2 |
+-------+-------+
| bbs | aa |
| bb | bb |
| cc | 0 |
+-------+-------+
3 rows in set (0.00 sec)
参考：
http://apps.hi.baidu.com/share/detail/22393623
本文转自 liang3391 51CTO博客，原文链接:http://blog.51cto.com/liang3391/815271
