Mysql JOIN Select查询，一个字段有Key，另一个字段有Value

Question

我必须表我正在尝试加入

表用户

id | name | age
----------------
 1 | Dave | 23
 2 | Dane | 65
 3 | Sam  | 15

表 user_profile

user_id | profile_key  | profile_value
------------------------------------------------
  1     | prfle.about  | This is about Dave bio
  1     | prfle.cntry  | Germany
  2     | prfle.email  | dane@somewhere.com
  1     | prfle.email  | dave@somewhere.com
  3     | prfle.about  | This something about Sam
  2     | prfle.cntry  | Canada

我想把这两个表选成这样

id | name | age  | prfle.about              | prfle.email        |  prfle.cntry
-------------------------------------------------------------------------
 1 | Dave | 23   | This is about Dave bio   | dave@somewhere.com | Germany
 2 | Dane | 65   |    null                  | dane@somewhere.com | Canada
 3 | Sam  | 15   | This something about Sam |  null              | null

这是我的查询

SELECT u.id, u.name, u.age, p.user_id, p.profile_key, p.profile_value
FROM user as u
LEFT JOIN user_profile as p
ON u.id = p.user_id

但是当我执行它时，我得到了类似笛卡尔连接的东西。

如果有人能帮助我，我会很高兴

Answer 1

如果您的 profile_key 值有限，那么您可以使用以下方法生成数据透视表作为

select
u.*,
max(case when up.profile_key = 'prfle.about' then up.profile_value end ) as `prfle.about`,
max(case when up.profile_key = 'prfle.cntry' then up.profile_value end ) as `prfle.cntry`,
max(case when up.profile_key = 'prfle.email' then up.profile_value end ) as `prfle.email`
from user u
left join user_profile up on up.user_id = u.id
group by u.id