mysql - 使用 CASE 将值与用户 ID 对齐

Question

我有一个表，它是一个自定义数据表，其中存在名称值对。下面是此表中针对个人的一些自定义数据的示例:

table custom_data
    user_id | custom_data_name | custom_data_value
    123 | initial contact date | 2014-01-01
    123 | subscription start date | 2014-02-02
    123 | favorite fruit | pears

我想创建一个查询来在个人级别解析它，这样我就可以像这样并排查看数据:

user_id | initial_contact_date | subscription_start_date
123 | 2014-01-01 | 2014-02-02

我试过这个:

SELECT 
        user_id,
        CASE WHEN base1.custom_data_name = 'initial contact date' THEN custom_data.data END AS initial_contact_date,
        CASE WHEN base1.custom_data_name = 'subscription start date' THEN custom_data.data END AS subscription_start_date
    FROM
        (
        SELECT * FROM custom_data
        WHERE custom_data_name = 'initial contact date' OR custom_data_name = 'subscription start date') base1
    GROUP BY user_id

但我很难解释结果。对于每个用户，结果会显示 initial_contact_date 或 subscription_start_date 中的一个值，但不会同时显示在一个记录中。

我该怎么做？

Answer 1

试试这个:

SELECT 
  user_id,
  MAX(CASE WHEN custom_data_name = 'initial contact date' THEN custom_data_value END) AS initial_contact_date,
  MAX(CASE WHEN custom_data_name = 'subscription start date' THEN custom_data_value END) AS subscription_start_date
  --MAX(CASE WHEN custom_data_name = 'favorite fruit' THEN custom_data_value END) AS favorite_fruit 
FROM custom_data
GROUP BY user_id

Sample SQL Fiddle