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
      ,CASE WHEN field_id ='Github' THEN field_value ELSE '' END AS Github
      ,CASE WHEN field_id ='Repository' THEN field_value ELSE ''  END AS Repository
      ,CASE WHEN field_id ='twitter' THEN field_value ELSE '' END AS Twitter

FROM package_stats, xf_user, xf_user_field_value, xf_user_profile
WHERE xf_user.username = package_stats.developer AND  xf_user_profile.user_id = xf_user.user_id AND xf_user_field_value.user_id = xf_user.user_id AND xf_user_field_value.field_value <> ''

输出以下内容：

developer   username    email      about     Github              Repository           Twitter
John Doe      jdoe    [email protected]   It's me   https://github....
John Doe      jdoe    [email protected]   It's me
John Doe      jdoe    [email protected]   It's me                       https://link....
John Doe      jdoe    [email protected]   It's me                                            https://twitter.com/...

如何将所有这些行混合成一行？

最佳答案

我相信您可以使用条件聚合：

SELECT ps.developer, u.username, u.email,
       up.homepage, up.location, up.about,
       MAX(CASE WHEN ufv.field_id = 'Github' THEN ufv.field_value END) AS Github,
       MAX(CASE WHEN ufv.field_id = 'Repository' THEN ufv.field_value END) AS Repository,
       MAX(CASE WHEN ufv.field_id = 'twitter' THEN ufv.field_value END) AS Twitter
FROM package_stats ps JOIN
     xf_user u
     ON u.username = ps.developer JOIN
     xf_user_profile up
     ON up.user_id = u.user_id JOIN
     xf_user_field_value ufv
     ON ufv.user_id = u.user_id
WHERE ufv.field_value <> ''
GROUP BY ps.developer, u.username, u.email,
         up.homepage, up.location, up.about;

笔记：


表别名使查询更易于编写和阅读。
所有列都应使用表别名进行限定，以避免歧义。
没有理由为此使用ELSE子句。如果没有匹配项，则该列为NULL（合理值）。
如果给定类别可能有多个字段，请使用GROUP_CONCAT()。