php - 动态行中的MySQL数据透视表查询

Question

我有两张 table 。就像

----UserTable---- 
id  user    email       
1   admin   admin@gmail.com
2   editor  editor@gmail.com

----NameTable---- 
name    userid  fullname    mobile
own         1   Rahim       012314
father      1   Karim       120120
mother      1   Florin      212021
own         2   Masum       012314
father      2   Nahid       120120
mother      2   Zane        212021

如何在 mysql 中的单个查询中获取所有数据(就像姓名、父亲、母亲、自己的名字)数据？

----Output Table----
id  user    email               name        fathername  mothername
1   admin   admin@gmail.com     Rahim       Karim       Florin
2   editor  editor@gmail.com    Masum       Nahid       Zane

Answer 1

如果始终有最多 3 列(本例中为自己、父亲和母亲)，则不必使用数据透视表

SELECT t.id,t.user,t.email,
       max(case when s.name = 'own' then s.fullname end) as name,
       max(case when s.name = 'father' then s.fullname end) as fathername,
       max(case when s.name = 'mother' then s.fullname end) as mothername
FROM UserTable t
INNER JOIN NameTable s ON(t.id = s.user_id)