mysql - 打印两个表的数据

Question

下面是我的表格。

create table tab1 ( id int, myname varchar(20));

insert into tab1 values 
(1, 'name 1'),
(2, 'name 2');

create table tab2 ( id int, tab2id int, type varchar(20), value varchar(20));
insert into tab2 values
(1,1,'phone','12345671'),
(1,2,'location','location 1'),
(2,3,'phone','12345672'),
(2,4,'location','location 2');

我想要的是如下。

myname   |   phone    | location  
 name 1  |  12345671  | location 1
 name 2  |  12345672  | location 2

知道如何完成这个任务吗？

dummy data to check query

Answer 1

select x.myname as name, a.value as Phone, b.value as Location
from tab1 x,
(select value, id
from tab2 
where type='phone' 
group by id)a,
(select value, id
from tab2 
where type='location' 
group by id)b
where x.id = a.id
and x.id = b.id;

SQL FIDDLE Here