mysql - SQL JOIN 作为单行，子值作为列，能够按子关系值进行 ORDER BY

Question

我不知道它的正确定义是什么，但它比常规关系连接更具动态性。

联系人:

id, first_name, last_name

字段:

id, handle, type

字段值:

id, field_id, contact_id, value

表字段在这里不是那么重要，但想提供上下文。

联系人表示例:

id   first_name   last_name
--   -----        --------
1    John         Doe
2    Jane         Smith

字段值表示例:

id   contact_id   field_id   value
--   -----        --------   ------
1    1            1          Boston
2    1            2          johndoe@mail.com
3    2            1          Seattle
3    2            2          janesmith@mail.com

在这个基本示例中，您可以看到有 2 个字段，一个用于位置(波士顿、西雅图)，一个用于电子邮件。当我将它们放入 JOIN 查询时，它们看起来像这样

SELECT * FROM contacts LEFT JOIN field_values ON contacts.id = field_values.contact_id;

contacts JOIN 字段值表示例:

id   first_name   last_name  field_id  value
--   -----        --------   ------    -------
1    John         Doe        1         Boston
1    John         Doe        2         johndoe@mail.com
2    Jane         Smith      1         Seattle
2    Jane         Smith      2         janesmith@mail.com

两个问题:

1) 如何按字段值排序。所以我想通过字段 id = 2 的字段电子邮件进行排序。

2) 是否可以为每个联系人和每个字段值获取一行作为新列？

示例:每个联系人单行？

id   first_name   last_name  field_id(2)          field_id(1)
--   -----        --------   ------               -------
1    John         Doe        johndoe@mail.com     Boston
2    Jane         Smith      janesmith@mail.com   Seattle

Answer 1

每个联系人单行:

SELECT 
    contacts.id,
    contacts.first_name,
    contacts.last_name,
    GROUP_CONCAT(IF(field_values.field_id = 2, field_values.value, NULL)) AS email,
    GROUP_CONCAT(IF(field_values.field_id = 1, field_values.value, NULL)) AS field_1
FROM contacts 
LEFT JOIN field_values ON contacts.id = field_values.contact_id
GROUP BY contacts.id
ORDER BY email;.  -- it is optional, only include if you want to sort result by ascending emails.