我有以下表格:
Name Type Value
---------------------
mike phone 123
mike address nyc
bob address nj
bob phone 333
我想要得到这样的结果:name value value
-------------------
mike nyc 123
bob nj 333
我该怎么做呢?
这被称为自连接。诀窍是使用别名。
select
address.name,
address.value as address,
phone.value as phone
from
yourtable as address left join
yourtable as phone on address.name = phone.name
where address.type = 'address' and
(phone.type is null or phone.type = 'phone')
该查询假定每个名称都有一个地址,但电话号码是可选的。
类似这样:
SELECT a.name AS name, phone, address
FROM (SELECT name, value AS phone FROM mytable WHERE type = "phone") AS a
JOIN (SELECT name, value AS address FROM mytable WHERE type = "address") AS b
ON(a.name = b.name);