mysql - 如何将一列分成几列？

Question

我有一张 table :

DataTable:
 id int,
 user_id int,
 field_id int,
 value varchar

数据示例:

id    user_id    field_id   value 
 1       1            1       'Peter'
 2       1            2       56
 3       1            3       100
 4       2            1       'Buzz'
 5       2            2       47
 6       2            3       120

我想通过 field_id 属性分隔 value 列:

Name    Age     Score
Peter   56      100
Buzz    47      120

我尝试过:

SELECT
    CASE
        WHEN field_id = 1 THEN  value
    END as Name,   
    CASE
        WHEN field_id = 2 THEN  value
    END as Age,
    CASE
        WHEN field_id = 3 THEN  value
    END as Score
    FROM Users u
    INNER JOIN DataTable t ON (t.user_id=u.id)

但是得到了:

Name    Age     Score
Peter   null     null
null    56       null
null    null     100
Buzz    null      null
null    47       null
null    null      120

有什么建议吗？

Answer 1

您可以将GROUP BY应用于您的查询。 Name 可能不是唯一的，您可以考虑按 id 分组:

SELECT id, MAX(Name) AS Name, MAX(Age) AS Age, MAX(Score) AS Score
FROM (
  SELECT u.id
  CASE
    WHEN field_id = 1 THEN  value
  END as Name,   
  CASE
    WHEN field_id = 2 THEN  value
  END as Age,
  CASE
    WHEN field_id = 3 THEN  value
  END as Score
  FROM Users u
  INNER JOIN DataTable t ON (t.user_id=u.id)
) AS sub
GROUP BY id;