基于列内容生成多列

Question

我需要生成一个连接两个与用户相关的表-用户和Usermeta的结果。这些来自Wordpress MySQL DB。

用户表-

ID user_email
1  abc@gmail.com
2  xyz@gmail.com
3  1sf@email.com

Usermeta表-

Umeta_ID user_id meta_key          meta_value
1000     1       billing_phone     9876443100
1001     1       billing_postcode  670001
1002     1       billing_address   Somewhere here, Delhi
1003     2       billing_phone     9876345188
1004     2       billing_postcode  650021
1005     2       billing_address   7th Lane, Bangalore
1003     3       billing_phone     7852562100
1004     3       billing_postcode  400521
1005     3       billing_address   Fancy Area, Mumbai

正如您可以看到的那样，Usermeta表中的user_id列引用用户表中的ID列。

我迫切需要一个MYSQL查询，它以这种方式给出结果-

ID user_email     billing_phone billing_postcode billing_address
1  abc@gmail.com  9876443100    670001           Somewhere here, Delhi
2  abc@gmail.com  9876345188    650021           7th Lane, Bangalore
3  1sf@gmail.com  7852562100    400521           Fancy Area, Mumbai

我试着用这样的两个连接来做一个联盟

SELECT 6ft_users.ID, 6ft_usermeta.meta_value as PHONE_NUMBER
FROM 6ft_users
INNER JOIN 6ft_usermeta
ON 6ft_users.ID=6ft_usermeta.user_id
WHERE 6ft_usermeta.meta_key LIKE 'billing_phone'
UNION
SELECT 6ft_users.ID, 6ft_usermeta.meta_value as POSTCODE
FROM 6ft_users
INNER JOIN 6ft_usermeta
ON 6ft_users.ID=6ft_usermeta.user_id
WHERE 6ft_usermeta.meta_key LIKE 'billing_postcode';

但这没有帮助，因为我只得到一个专栏- PHONE_NUMBER。我尝试了一些其他的事情，但都没有结果。请帮帮我。提前感谢！

Answer 1

试试这个：

SELECT U.ID, 
       U.user_email, 
       MAX(IF(UM.meta_key = 'billing_phone', UM.meta_value, '')) AS billing_phone, 
       MAX(IF(UM.meta_key = 'billing_postcode', UM.meta_value, '')) AS billing_postcode, 
       MAX(IF(UM.meta_key = 'billing_address', UM.meta_value, '')) AS billing_address
FROM 6ft_users U
LEFT JOIN 6ft_usermeta UM ON U.ID = UM.user_id
GROUP BY U.ID