mysql - 在 MySQL 中按相邻列中的值查询

Question

我相信所有这种性质的问题都是这样开始的……我有两个表:fields 和data。 Fields 描述(不存在的)表的列名，data 包含该(不存在的)表的数据。像这样...

字段:

    +----+---------+-------------+---------------+-------+----------+
    | ID | F_ORDER | NAME        | LABEL         | VALUE | TYPE     |
    +----+---------+-------------+---------------+-------+----------+
    |  1 |       1 | IS_EMPLOYEE | Region        |       | checkbox |
    |  2 |       3 | EM_AVATAR   | Avatar        |       | avatar   |
    |  3 |       4 | EM_JOBTITLE | Job Title     |       | text     |
    |  4 |       5 | EM_COMPANY  | Company       |       | text     |
    |  5 |       6 | EM_PHONE    | Phone         |       | text     |
    |  6 |       2 | EM_ORDER    | Display Order | 5     | text     |
    +----+---------+-------------+---------------+-------+----------+

数据:

    +-----+----------+---------+--------------------------------------+
    | ID  | FIELD_ID | USER_ID | VALUE                                |
    +-----+----------+---------+--------------------------------------+
    |   5 |        1 |       1 | YES                                  |
    |   6 |        2 |       1 |                                      |
    |   7 |        3 |       1 | Owner                                |
    |   8 |        4 |       1 | Acme, Inc.                           |
    |   9 |        5 |       1 | 123-456-7987                         |
    | 150 |        5 |      31 | 123-623-5555                         |
    | 149 |        4 |      31 | Acme, Inc.                           |
    | 148 |        3 |      31 | Sales and Customer Support           |
    | 147 |        2 |      31 |                                      |
    | 146 |        1 |      31 | YES                                  |
    |  26 |        1 |       6 | NO                                   |
    |  27 |        2 |       6 | http://example.com/avi/avi.jpeg      |
    |  28 |        3 |       6 | CEO                                  |
    |  29 |        4 |       6 | Acme                                 |
    |  30 |        5 |       6 | (123) 734-5555                       |
    |  31 |        1 |       7 | NO                                   |
    |  32 |        2 |       7 | http://example.com/avi/avi.jpeg      |
    |  33 |        3 |       7 | VP, Services                         |
    |  34 |        4 |       7 | Acme                                 |
    |  35 |        5 |       7 | (913) 963-5555                       |
    |  36 |        1 |      14 | NO                                   |
    |  37 |        2 |      14 | http://example.com/avi/avi.jpeg      |
    |  38 |        3 |      14 | Senior Accountant                    |
    |  39 |        4 |      14 | Acme                                 |
    |  40 |        5 |      14 | (123) 213-5555                       |
    |  41 |        1 |      10 | NO                                   |
    |  42 |        2 |      10 | http://example.com/avi/avi.jpeg      |
    |  43 |        3 |      10 | President                            |
    |  44 |        4 |      10 | Acme                                 |
    |  45 |        5 |      10 | (123) 734-5555                       |
    |  46 |        1 |      12 | NO                                   |
    |  47 |        2 |      12 | http://example.com/avi/avi.jpeg      |
    |  48 |        3 |      12 | Services Supervisor                  |
    |  49 |        4 |      12 | Acme                                 |
    |  50 |        5 |      12 | (123) 573-5555                       |
    |  51 |        1 |      11 | NO                                   |
    |  52 |        2 |      11 | http://example.com/avi/avi.jpeg      |
    |  53 |        3 |      11 | Operations Supervisor                |
    |  54 |        4 |      11 | Acme                                 |
    |  55 |        5 |      11 | (123) 259-5555                       |
    |  56 |        1 |       8 | NO                                   |
    |  57 |        2 |       8 | http://example.com/avi/avi.jpeg      |
    |  58 |        3 |       8 | General Information                  |
    |  59 |        4 |       8 | Acme                                 |
    |  60 |        5 |       8 | (123) 213-5555                       |
    |  61 |        1 |       9 | NO                                   |
    |  62 |        2 |       9 | http://example.com/avi/avi.jpeg      |
    |  63 |        3 |       9 | VP, Sales                            |
    |  64 |        4 |       9 | Acme                                 |
    |  65 |        5 |       9 | (123) 210-5555                       |
    +-----+----------+---------+--------------------------------------+

我正在寻找的查询的基本措辞是:我想要所有员工的所有信息 (IS_EMPLOYEE = "YES") 并按他们的显示顺序列排序 (EM_ORDER).

到目前为止，我的查询无处可去。我得到的结果有点像这样:

    +--------+------------+------------+-------+---------+-------+
     ID        FIELD_ID    NAME         LABEL   TYPE       VALUE
    +--------+------------+------------+-------+---------+-------+
     7         1           IS_EMPLOYEE  Region  checkbox   YES
    +--------+------------+------------+-------+---------+-------+

我需要的是这样的结果:

    +-------+------------+---------+-----------+----------+-------------+--------+
     USER_ID IS_EMPLOYEE  EM_AVATAR EM_JOBTITLE EM_COMPANY EM_PHONE      EM_ORDER   
    +-------+------------+---------+-----------+----------+-------------+--------+
     6       YES           http://  CEO         Acme       123-123-555   5 
    +-------+------------+---------+-----------+----------+-------------+--------+ 

当然，我正在尝试将其作为可用数组($results['user_id']['jobtitle'] 等)全部返回到 PHP 中。我可以得到 一切 并使用 PHP 完成它，但我正在尝试学习 MySQL，我认为这是一种比执行多个 foreach block 更快的方法……尽管我可能是错的。

在此先感谢您的帮助。

Answer 1

您的数据表组织在 Entity-Attribute-Value 中方式，这不是存储关系数据的有效方式。难怪用 SQL 将其检索到每个属性一列的常规行中很尴尬。

要使用 SQL 执行此操作，您基本上需要执行一个数据透视查询。这很难编写并且执行起来效率极低。

您最好获取员工的所有数据:

SELECT e.*, f.label
FROM Data AS is_employee
JOIN Data AS e USING (user_id)
JOIN Fields AS f ON e.field_id = f.id
WHERE (is_employee.field_id, is_employee.value) = (1, 'Yes');

然后在 PHP 代码中将其重组为一组关联数组:

while ($row = $stmt->fetch()) {
  $data[ $row['user_id'] ][ $row['label'] ] = $row['value'];
}