mysql - 将值转换为列SQL

我正在使用以下SQL查询：

SELECT package_stats.developer, xf_user.username, xf_user.email, xf_user_profile.homepage, xf_user_profile.location, xf_user_profile.about, xf_user_field_value.field_id, xf_user_field_value.field_value
FROM package_stats, xf_user, xf_user_field_value, xf_user_profile
WHERE xf_user.username = package_stats.developer AND xf_user_field_value.user_id = xf_user.user_id AND xf_user_profile.user_id = xf_user.user_id

它拉着这样的东西：
我的问题是，是否可以使用SQL查询将不同的field_id值转换成列，这样我就只有一行。

最佳答案

如果需要同一行的结果，则每次需要值时都应加入xf_user_字段值，例如：

SELECT package_stats.developer
      , xf_user.username
      , xf_user.email
      , xf_user_profile.homepage
      , xf_user_profile.location
      , xf_user_profile.about
      , v1.field_value as Github
      , v2.field_value as Repository
      , v3.field_value as UserRole
      , v4.field_value as aim
      , v5.field_value as twitter

FROM package_stats
INNER JOIN  xf_user ON xf_user.username = package_stats.developer
INNER JOIN xf_user_profile  ON xf_user_profile.user_id = xf_user.user_id
INNER JOIN  xf_user_field_value v1 ON v1.user_id = xf_user.user_id and 'v1.field_id' = 'Github'
INNER JOIN  xf_user_field_value v2 ON v2.user_id = xf_user.user_id and 'v2.field_id' = 'Repository'
INNER JOIN  xf_user_field_value v3 ON v3.user_id = xf_user.user_id and 'v3.field_id' = 'UserRole'
INNER JOIN  xf_user_field_value v4 ON v4.user_id = xf_user.user_id and 'v4.field_id' = 'aim'
INNER JOIN  xf_user_field_value v5 ON v5.user_id = xf_user.user_id and 'v5.field_id' = 'twitter'

关于mysql - 将值转换为列SQL，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/50191381/