mysql - 如何将行显示为具有其他值的动态列？

Question

我想显示 collect_id = 2 的订单项我想将与每个 order_item 相关的所有字段显示为具有值的列。

这些是表格和结果:

+-------------------------------+
|         order_item            |
+-------------------------------+
| oi_id  oi_price oi_collect_id |
| 1         100         2       |
| 2         30          2       |
| 3         55          3       |
| 4         70          4       |
| 5         220         2       |
| 6         300         4       |
+-------------------------------+

+-----------------------------------+
|            field_value            |
+-----------------------------------+
| v_value    v_fk_field_id oi_fk_id |
|  Peter             1        1     |
|  Lagaf             2        1     |
|  Football          3        1     |
|  Male              4        1     |
|  12345678          5        1     |
|  Frank             1        2     |
|  Loran             2        2     |
|  Tennis            3        2     |
|  Male              4        2     |
|  11223658          5        2     |
|  Nathali           1        5     |
|  Waton             2        5     |
|  Reading           3        5     |
+-----------------------------------+

oi_fk_id : foreign key ref(order_item.oi_id)
v_fk_field_id : foreign key ref(field.f_id)

+--------------------+
|       field        |
+--------------------+
| f_id      f_label  |
| 1          surname |
| 2          name    |
| 3          hobbies |
| 4          sex     |
| 5          phone   |
+--------------------+

+-----------------------------------------------------------------------------+
|                                   Result                                    |
+-----------------------------------------------------------------------------+
|  oi_id  oi_price oi_collect_id surname    name    hobbies    sex    phone   |
|  1         100         2        Peter     Lagaf   Football   Male  12345678 |
|  2         30          2        Frank     Loran    Tennis    Male  11223658 |
|  5         220         2       Nathali    Waton   Reading    null    null   |
+-----------------------------------------------------------------------------+

重要:表字段不只包含这5个字段(name, surname, hobbies, sex, phone)，还可以包含很多其他的，开发者可能不知道，表上对应的值也是一样“字段值”。

PS:我没有把字段标签做成表格的列，因为它们是动态的，没有限制，在前端应用中，用户可以随意添加新的字段。

Answer 1

您可以利用动态旋转来获得结果:

SELECT      GROUP_CONCAT(t.line)
    FROM    (
        SELECT      CONCAT('MAX(IF(t.l=''', f.f_label, ''',t.v,NULL)) AS ', f.f_label) AS line
            FROM    field f
        ) AS t
    INTO @dynamic;

SELECT      CONCAT('SELECT t.oi_id, t.oi_price, t.oi_collect_id,', 
            @dynamic, 
            ' FROM ( SELECT oi.*, f.f_label AS l, fv.v_value AS v FROM order_item oi JOIN field_value fv ON fv.oi_fk_id = oi.oi_id JOIN field f ON f.f_id = fv.v_fk_field_id WHERE oi.oi_collect_id = 2 ) AS t GROUP BY t.oi_id;')
    INTO @sql;

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

但是它受 GROUP_CONCAT function 的限制:

The result is truncated to the maximum length that is given by the group_concat_max_len system variable, which has a default value of 1024. The value can be set higher, although the effective maximum length of the return value is constrained by the value of max_allowed_packet.

编辑 - 为什么需要 MAX 函数？

为了解决给定的问题，我们根据 field 表的内容创建一个动态查询。

对于给定的示例数据，不使用 MAX 函数的查询将是:

SELECT      t.oi_id,
            t.oi_price,
            t.oi_collect_id,
            IF(t.l='surname', t.v, NULL) AS surname,
            IF(t.l='name', t.v, NULL) AS name,
            IF(t.l='hobbies', t.v, NULL) AS hobbies,
            IF(t.l='sex', t.v, NULL) AS sex,
            IF(t.l='phone', t.v, NULL) AS phone
    FROM    (
        SELECT      oi.*,
                    f.f_label AS l,
                    fv.v_value as V
            FROM    order_item oi
            JOIN    field_value fv
                ON  fv.oi_fk_id = oi.oi_id
            JOIN    field f
                ON  f.f_id = fv.v_fk_field_id
            WHERE   oi.oi_collect_id = 2
        ) AS t;

这会导致:

+-------+----------+---------------+---------+-------+----------+------+----------+
| oi_id | oi_price | oi_collect_id | surname | name  | hobbies  | sex  | phone    |
+-------+----------+---------------+---------+-------+----------+------+----------+
|     1 |      100 |             2 | Peter   | NULL  | NULL     | NULL | NULL     |
|     2 |       30 |             2 | Frank   | NULL  | NULL     | NULL | NULL     |
|     5 |      220 |             2 | Nathali | NULL  | NULL     | NULL | NULL     |
|     1 |      100 |             2 | NULL    | Lagaf | NULL     | NULL | NULL     |
|     2 |       30 |             2 | NULL    | Loran | NULL     | NULL | NULL     |
|     5 |      220 |             2 | NULL    | Waton | NULL     | NULL | NULL     |
|     1 |      100 |             2 | NULL    | NULL  | Football | NULL | NULL     |
|     2 |       30 |             2 | NULL    | NULL  | Tennis   | NULL | NULL     |
|     5 |      220 |             2 | NULL    | NULL  | Reading  | NULL | NULL     |
|     1 |      100 |             2 | NULL    | NULL  | NULL     | Male | NULL     |
|     2 |       30 |             2 | NULL    | NULL  | NULL     | Male | NULL     |
|     1 |      100 |             2 | NULL    | NULL  | NULL     | NULL | 12345678 |
|     2 |       30 |             2 | NULL    | NULL  | NULL     | NULL | 11223658 |
+-------+----------+---------------+---------+-------+----------+------+----------+

这是一个中间结果，其中每一行包含一个 字段 的值和其他字段的 NULL。 MAX 函数与 GROUP BY 子句一起用于组合涉及一个 order item 的多行，它选择非 null值。它可以替换为 MIN 函数，这也将有利于现有值而不是 null。