mysql 多个关联查询

## 表结构 ##
tbl_a
+-------+---------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| aaa | int(11) | YES | | NULL | |
| bbb | int(11) | YES | | NULL | |
| ccc | int(11) | YES | | NULL | |
+-------+---------+------+-----+---------+----------------+

CREATE TABLE `tbl_a` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`aaa` int(11) DEFAULT NULL,
`bbb` int(11) DEFAULT NULL,
`ccc` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

tbl_b
+-------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| k | int(11) | NO | | NULL | |
| v | varchar(255) | NO | | NULL | |
+-------+--------------+------+-----+---------+-------+

CREATE TABLE `tbl_b` (
`k` int(11) NOT NULL,
`v` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

## 表数据 ##

select * from tbl_a;
+----+-----+-----+-----+
| id | aaa | bbb | ccc |
+----+-----+-----+-----+
| 1 | 3 | 4 | 5 |
+----+-----+-----+-----+

select * from tbl_b;
+---+------+
| k | v |
+---+------+
| 3 | sad |
| 4 | cat |
| 5 | fast |
+---+------+

## 查询语句 1 ##

SELECT id, (SELECT v FROM tbl_b WHERE k = aaa)as aaa, (SELECT v FROM tbl_b WHERE k = bbb)as bbb, (SELECT v FROM tbl_b WHERE k = ccc)as ccc from tbl_a;

SELECT id,
(SELECT v FROM tbl_b WHERE k = aaa)as aaa, 
(SELECT v FROM tbl_b WHERE k = bbb)as bbb, 
(SELECT v FROM tbl_b WHERE k = ccc)as ccc 
from tbl_a;

 

+----+-----+-----+------+
| id | aaa | bbb | ccc |
+----+-----+-----+------+
| 1 | sad | cat | fast |
+----+-----+-----+------+

## 查询语句 2 ##

select ifnull((select b.v id from tbl_b b where b.k=a.id), a.id ) id, ifnull((select b.v aaa from tbl_b b where b.k=a.aaa), a.aaa) aaa, ifnull((select b.v bbb from tbl_b b where b.k=a.bbb), a.bbb) bbb,    ifnull((select b.v ccc from tbl_b b where b.k=a.ccc), a.ccc) ccc from tbl_a a ;

select 
ifnull((select b.v id from tbl_b b where b.k=a.id), a.id ) id, 
ifnull((select b.v aaa from tbl_b b where b.k=a.aaa), a.aaa) aaa,
ifnull((select b.v bbb from tbl_b b where b.k=a.bbb), a.bbb) bbb,
ifnull((select b.v ccc from tbl_b b where b.k=a.ccc), a.ccc) ccc
from tbl_a a ;

+----+-----+-----+------+
| id | aaa | bbb | ccc |
+----+-----+-----+------+
| 1 | sad | cat | fast |
+----+-----+-----+------+

## 查询语句 3 ##

select a.id, b.v, c.v, d.v from tbl_a as a left join tbl_b as b on a.aaa=b.k left join tbl_b as c on a.bbb=c.k left join tbl_b as d on a.ccc=d.k ;

select a.id, b.v, c.v, d.v from tbl_a as a 
left join tbl_b as b on a.aaa=b.k 
left join tbl_b as c on a.bbb=c.k 
left join tbl_b as d on a.ccc=d.k ;

+----+-----+-----+------+
| id | v | v | v |
+----+-----+-----+------+
| 1 | sad | cat | fast |
+----+-----+-----+------+