在MySQL上将多行合并为一行和多列 [英] Merging multiple rows into one row and multiple columns on mysql

问题描述

我正在使用MYSQL，需要提取用户数据以将其拉入视图.我将在电子邮件客户端中使用数据，因此无法在应用程序层中执行此操作.

I am working in MYSQL and need to extract user data to be pulled into a view. I will be using the data in an email client, so I cannot do this in the app layer.

问题在于用户的每个数据字段都包含在单独的行中(这是Wordpress设置数据结构的方式).

The problem is that each data field for the user is contained in separate rows (this is how Wordpress sets up the data structure).

例如，wp_usermeta为每个用户提供多行数据，如下所示:

For example, wp_usermeta has multiple rows of data for each user like this:

user_id   meta_key       meta_data
   2      first_name     Jane
   2      last_name      Austin
   2      email          [e-mail]
   3      first_name     Jack
   3      last_name      Palmer
   3      email          [e-mail]

我需要将数据合并为一行，分别是几个字段，如下所示:

I need the data to be combined into one row, separate fields, like this:

user_id  first_name  last_name  email
2        Jane        Austin     [e-mail]
3        Paul        Parker     [e-mail]

我已经四处搜寻，在任何地方都找不到这个确切的问题(我发现了很多串联，但这不是我所需要的).

I have searched around and cannot find this exact problem anywhere (I found a lot of concatenation, but that is not what I need).

推荐答案

如果只有这些列是您关注的列，那么它将对您有用:

If these are the only columns you are concerned with, this will work for you:

SELECT um.user_id
   , fn.meta_data AS first_name
   , ln.meta_data AS last_name
   , e.meta_data AS email
FROM wp_userMeta AS um
LEFT JOIN wp_user_Meta AS fn ON um.user_id = fn.user_id
   AND fn.meta_key = 'first_name'
LEFT JOIN wp_user_Meta AS ln ON um.user_id = ln.user_id
   AND ln.meta_key = 'last_name'
LEFT JOIN wp_user_Meta AS e ON um.user_id = e.user_id
   AND e.meta_key = 'email'

这篇关于在MySQL上将多行合并为一行和多列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！