SQL JOIN 作为单行，子值作为列并且能够按子关系值排序 [英] SQL JOIN as single row with child values as columns and ability to ORDER BY child relationship value

问题描述

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

I don't know what the right definition of this is, but its more dynamic than a regular relationship join.

联系人:

id, first_name, last_name

字段:

id, handle, type

field_values:

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          [e-mail]
3    2            1          Seattle
3    2            2          [e-mail]

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

In this basic example, you can see that there are 2 fields, one for location (boston, seattle) and one for email. When I put them into a JOIN query they look like this

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

联系人 JOIN 字段值表示例:

id   first_name   last_name  field_id  value
--   -----        --------   ------    -------
1    John         Doe        1         Boston
1    John         Doe        2         [e-mail]
2    Jane         Smith      1         Seattle
2    Jane         Smith      2         [e-mail]

两个问题:

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

1) How do I ORDER BY the field value. So I want to order by the field email which is field id = 2.

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

2) Is it possible to get a single row for each contact and each field value as a new column?

示例:每个联系人单行?

id   first_name   last_name  field_id(2)          field_id(1)
--   -----        --------   ------               -------
1    John         Doe        [e-mail]     Boston
2    Jane         Smith      [e-mail]   Seattle

推荐答案

每个联系人单行:

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.

这篇关于SQL JOIN 作为单行，子值作为列并且能够按子关系值排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！