mysql - sql连接同一个表中的2行

Question

我有下表:

Name   Type     Value
---------------------
mike   phone    123    
mike   address  nyc    
bob    address  nj    
bob    phone    333

我想要这样的结果:

name  value  value
-------------------
mike  nyc    123
bob   nj     333

我该怎么做？

Answer 1

它被称为自连接。诀窍是使用别名。

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')

查询假设每个名字都有一个地址，但电话号码是可选的。