sql在同一个表中连接2行

I have the following table:

我有下表:

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

I want to have the result like this:

我想得到这样的结果:

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

How can I do it?

我该怎么做?

2 个解决方案

#1

it is called a self-join. the trick is to use aliases.

它被称为自我加入。诀窍是使用别名。

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

The query assumes that each name has an address, but phone numbers are optional.

该查询假定每个名称都有一个地址,但电话号码是可选的。

#2

Something like this:

像这样的东西:

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

#1