mysql特殊查询

 

一、问题描述---有a、b两张表，a表有字段aid,aname；b表有字段bid,bname。如下所图：

aid	aname
1	2
2	5
7	9
bid	bname
1	7
3	4
6	4

 

现在需要通过sql语句查询出已下格式的结果：

id	aname	bname
1	2	7
2	5	0
3	0	4
6	0	4
7	9	0

 

二、解决方案

mysql中不支持完全外连接，但是笛卡尔积的方式也不能完整查出我们要的数据，会有很多干扰数据。不妨先通过一个左连接、一个右连接，然后union起来，查询语句如下

select * from a left join b on a.aid =b.bid
union 
select * from a right join b on a.aid =b.bid

 

查询结果如下

aid	bid	aname	bname
1	1	2	7
2		5	0
	3	0	4
	6	0	4
7		9	0

 

查询出的结果并不是我们想要的，我们想把aid 和bid两列合并，这时候就要用到mysql中的和并列函数，查询sql如下

select substring_index( concat_ws(',',aid,bid) ,',',-1) as id,aname,bname 
from
(select * from a left join b on a.aid =b.bid) as aa
union 
select substring_index( concat_ws(',',aid,bid) ,',',-1) as id,aname,bname 
from
(select * from a right join b on a.aid =b.bid) as bb

  查询结果如下：

id	aname	bname
1	2	7
2	5	0
3	0	4
6	0	4
7	9	0

 

三、遗留问题

不过这种方式毕竟不是最优化的方式，但是苦于我一直想不出优化的方式。一开始想走捷径，就想两张表直接union，通过行转列的方式。尝试的sql有：

select id, group_concat(name) from 	
(select aid as id,aname as name from a union select bid,bname as name from b) as c	
group by id	

查询结果如下：

id	name
1	2,7
2	5
3	4
6	4
7	9

这样虽然查出了数据，但是无法精确name哪个值是哪个id的