为具有相同ID的多行选择不同的值

I have a table that looks like this:

我有一个看起来像这样的表：

ID | FIELD_NAME   | VALUE
23 |  sign_up     |  yes
23 |  first_name  |  Fred
23 |  street      |  Barber Lane
24 |  sign_up     |  no
24 |  first_name  |  Steve
24 |  street      |  Camaro St.
25 |  sign_up     |  yes
25 |  first_name  |  Larry
25 |  street      |  Huckleberry Ave

I want to run a query that will select unique ID's and the values as named columns so it would appear like so:

我想运行一个查询，它将选择唯一的ID和值作为命名列，所以它看起来像这样：

ID   |   SIGN_UP   | FIRST_NAME  |  STREET           |
23   |     yes     |    Fred     |  Barber Lane      |
24   |     no      |    Steve    |  Camaro St.       |
25   |     yes     |    Larry    |  Huckleberry Ave. |

Any help would be much appreciated!!

任何帮助将非常感激！！

5 个解决方案

#1

You can use this simple solution:

您可以使用这个简单的解决方案

SELECT DISTINCT
    a.id,
    b.value AS SIGN_UP,
    c.value AS FIRST_NAME,
    d.value AS STREET
FROM tbl a
LEFT JOIN tbl b ON a.id = b.id AND b.field_name = 'sign_up'
LEFT JOIN tbl c ON a.id = c.id AND c.field_name = 'first_name'
LEFT JOIN tbl d ON a.id = d.id AND d.field_name = 'street'

Just to be safe, I made the joins LEFT JOIN's because I do not know if an id can have missing fields, in which case they will show up as NULL in our derived columns.

为了安全起见，我加入了LEFT JOIN，因为我不知道id是否可以丢失字段，在这种情况下它们将在我们的派生列中显示为NULL。

SQL-Fiddle Demo

SQL-Fiddle演示

#2

You could also try pivoting with the help of grouping and conditional aggregating:

您还可以在分组和条件聚合的帮助下尝试旋转：

SELECT
  ID,
  MAX(CASE FIELD_NAME WHEN 'sign_up'    THEN VALUE END) AS SIGN_UP,
  MAX(CASE FIELD_NAME WHEN 'first_name' THEN VALUE END) AS FIRST_NAME,
  MAX(CASE FIELD_NAME WHEN 'street'     THEN VALUE END) AS STREET
FROM atable
GROUP BY
  ID
;

#3

Adapted from another answer by me:

改编自我的另一个答案：

SELECT ids.ID AS ID,
       sign_up.VALUE AS SIGN_UP,
       first_name.VALUE AS FIRST_NAME,
       street.VALUE AS STREET
FROM (SELECT DISTINCT ID FROM tableName) AS ids
     LEFT JOIN tableName AS sign_up
            ON (sign_up.ID = ids.ID AND
                sign_up.FIELD_NAME = 'sign_up')
     LEFT JOIN tableName AS first_name
            ON (first_name.ID = ids.ID AND
                first_name.FIELD_NAME = 'first_name')
     LEFT JOIN tableName AS street
            ON (street.ID = ids.ID AND
                street.FIELD_NAME = 'street')

The left joins will ensure that missing values will result in NULL cells, instead of an omission of the whole row. Not sure whether that is important in your application. If it is not, you can use an inner join and in particular get rid of the subquery to select all unique IDs. See my original answer from which I derived this.

左连接将确保缺少的值将导致NULL单元格，而不是省略整行。不确定这在您的应用程序中是否重要。如果不是，您可以使用内部联接，特别是删除子查询以选择所有唯一ID。看到我从中得出这个的原始答案。

#4

Am not sure there is a Pivot/Unpivot feature in MySQL. Try this:

我不确定MySQL中是否存在Pivot / Unpivot功能。尝试这个：

SELECT a.ID, 
       c.FIELD_NAME AS SIGN_UP,
       a.FIELD_NAME AS FIRST_NAME,
       b.FIELD_NAME AS STREET

  FROM <YOUR-TABLE> a LEFT JOIN <YOUR-TABLE> b
        ON a.ID = b.ID 
     AND a.FIELD_NAME = 'first_name'
     AND b.FIELD_NAME = 'street' LEFT JOIN <YOUR-TABLE> c
        ON c.ID = a.ID
     AND c.FIELD_NAME = 'sign_up'

#5

One approach is to use a correlated subquery to return each field value as a column,

一种方法是使用相关子查询将每个字段值作为列返回，

SELECT t.id
     , (SELECT f1.value FROM mytable f1 
         WHERE f1.id = t.id AND f1.field_name = 'sign_up' 
         ORDER BY f1.value LIMIT 1
       ) AS SIGN_UP
     , (SELECT f2.value FROM mytable f2 
         WHERE f2.id = t.id AND f2.field_name = 'first_name' 
         ORDER BY f2.value LIMIT 1
       ) AS FIRST_NAME
     , (SELECT f3.value FROM mytable f3 
         WHERE f3.id = t.id AND f3.field_name = 'street'
         ORDER BY f3.value LIMIT 1
       ) AS STREET
  FROM (SELECT s.id
          FROM mytable s
         GROUP BY s.id
         ORDER BY s.id
       ) t

This isn't the only way, but it's a workable approach, especially if you are concerned that you will get exactly four columns returned, and that they will be returned in a specific sequence.

这不是唯一的方法，但它是一种可行的方法，特别是如果您担心将返回四列，并且它们将按特定顺序返回。

Note that this approach works when a particular field_name is "missing" for a particular ID (it will return a NULL in place of a value). It also works if there are multiple occurrences of the same field_name for a particular ID. (This query will return only one of them, and disregard the other.)

请注意，当特定ID的“特定field_name”缺失时，此方法会起作用（它将返回NULL而不是值）。如果特定ID出现多次相同的field_name，它也可以工作。（此查询将只返回其中一个，而忽略另一个。）

This same result set can also be obtained with a query written like this:

使用如下所示的查询也可以获得相同的结果集：

SELECT t.id          AS ID
     , f1.sign_up    AS SIGN_UP
     , f2.first_name AS FIRST_NAME
     , f3.street     AS STREET       
  FROM (SELECT s.id
          FROM mytable s
         GROUP BY s.id
         ORDER BY s.id
       ) t
   LEFT      
   JOIN (SELECT s1.id
              , MIN(s1.value) AS sign_up
           FROM mytable s1
          WHERE s1.field_name = 'sign_up'
            AND s1.value IS NOT NULL 
          GROUP BY s1.id
        ) f1
     ON f1.id = t.id   
   LEFT
   JOIN (SELECT s2.id
              , MIN(s2.value) AS first_name
           FROM mytable s2
          WHERE s2.field_name = 'first_name'
            AND s2.value IS NOT NULL
          GROUP BY s2.id
        ) f2
     ON f2.id = t.id
   LEFT
   JOIN (SELECT s3.id
              , MIN(s3.value) AS street
           FROM mytable s3
          WHERE s3.field_name = 'street'
            AND s3.value IS NOT NULL
          GROUP BY s3.id
        ) f3
     ON f3.id = t.id

With other queries, ensure you are getting the desired behavior when a field_name is "missing" for a given ID, or when there are duplicate field_name for a given ID, or when there are additional field_name values in the table which you are not concerned with.

对于其他查询，确保在给定ID的“field missing”缺失时，或者对于给定ID存在重复的field_name时，或者在表中存在您不关心的其他field_name值时，您将获得所需的行为。

#1