mysql 连表查询

表test_Info;
+----------+-----+---------+
| name     | sex | address |
+----------+-----+---------+
| zhangsan | 1   | 2       |
| lisi     | 3   | 4       |
+----------+-----+---------+

表 test_Code;
+----+---------+
| BM | NR      |
+----+---------+
| 1  | nan     |
| 2  | hainan  |
| 3  | nv      |
| 4  | sichuan |
+----+---------+

 

mysql> select a.name,(select NR from test_Code where BM=a.sex) as sex,(select NR from test_Code where BM=a.address) as address from test_Info a;
+----------+------+---------+
| name     | sex  | address |
+----------+------+---------+
| zhangsan | nan  | hainan  |
| lisi     | nv   | sichuan |
+----------+------+---------+