MySQL列名作为许多与连接表关系的字段名

bit of a complicated one here. I've been looking around at a way to use results as column names, and whilst it seems possible, all the examples I've found use a very simple table design.

有点复杂。我一直在寻找一种将结果用作列名的方法，尽管看起来可能，但我发现的所有示例都使用了非常简单的表设计。

What I've got is 3 tables.

我有3张桌子。

We have an 'order' table, which can then have 'order_extras'. The 'extras' table stores the names and prices of the extras, and the 'order_extras' basically cointains a primary key, the order id and the extra id.

我们有一个“order”表，它可以有“order_extras”。“附加”表存储附加项的名称和价格，而“order_extras”基本上同时包含主键、订单id和额外id。

A rough graphical representation of this is as follows:

这方面的粗略图形表示如下:

Using this as an example, lets assume the 'extras' table is populated with 3 extra items, the name and price are irrelevant at this stage.

以这个为例，假设“extras”表中有3个额外的项，在这个阶段，名称和价格是不相关的。

What I want to do, is get all the orders, but with extra columns for the name of each extra item. If the item has been purchased (aka linked in the order_extras table) it'll show the price, otherwise it'll be empty/null.

我想做的是，得到所有的订单，但是每个额外项目的名称都有额外的列。如果该商品已被购买(在order_extras表中链接)，它将显示价格，否则它将为空/空。

Is this even possible? I've been looking into pivot tables but the information on this sort of thing seems a bit shaky. Any info or suggestions would be greatly appreciated!

这是可能吗?我一直在研究数据透视表，但这类信息似乎有点不可靠。如有任何消息或建议，将不胜感激!

Example data

示例数据

Extras:

附加功能:

+----+------------------+--------+
| id |       name       | price  |
+----+------------------+--------+
|  1 | Insurance        | 59.95  |
|  2 | Lifetime Updates | 79.95  |
|  3 | Phone Support    | 124.95 |
+----+------------------+--------+

Orders:

订单:

+----+------------+
| id |  customer  |
+----+------------+
|  1 | John Smith |
|  2 | Bob Newbie |
|  3 | Bill Jobs  |
|  4 | Ray Stantz |
+----+------------+

order_extras:

order_extras:

+----+----------+----------+
| id | order_id | extra_id |
+----+----------+----------+
|  1 |        4 |        2 |
|  2 |        3 |        1 |
|  3 |        3 |        3 |
|  4 |        1 |        1 |
+----+----------+----------+

Desired Output:

期望的输出:

+----------+----------------+-----------+------------------+---------------+
| order.id | order.customer | Insurance | Lifetime Updates | Phone Support |
+----------+----------------+-----------+------------------+---------------+
|        1 | John Smith     | 59.95     | 0                | 0             |
|        2 | Bob Newbie     | 0         | 0                | 0             |
|        3 | Bill Jobs      | 59.95     | 0                | 124.95        |
|        4 | Ray Stantz     | 0         | 79.95            | 0             |
+----------+----------------+-----------+------------------+---------------+

2 个解决方案

#1

Unfortunately, MySQL does not have a pivot function, but is can be replicated using an aggregate function with a CASE expression.

不幸的是，MySQL没有一个主函数，但是可以使用一个用例表达式的聚合函数复制。

If you have a known number of extras, then you can hard-code the query:

如果您有已知数量的额外功能，那么您可以硬编码查询:

select o.id,
  o.customer,
  max(case when e.name = 'Insurance' then e.price else 0 end) Insurance,
  max(case when e.name = 'Lifetime Updates' then e.price else 0 end) `Lifetime Updates`,
  max(case when e.name = 'Phone Support' then e.price else 0 end) `Phone Support`
from orders o
left join order_extras oe
  on o.id = oe.order_id
left join extras e
  on oe.extra_id = e.id
group by o.id, o.customer

See SQL Fiddle with Demo.

参见SQL小提琴演示。

For your situation, it seems like you will have an unknown number of values. If that is the case, then you will need to use a prepared statement to generate dynamic sql:

对于您的情况，似乎您将拥有未知数量的值。如果是这样，那么您将需要使用一个准备好的语句来生成动态sql:

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'max(CASE WHEN e.name = ''',
      name,
      ''' THEN e.price else 0 END) AS `',
      name, '`'
    )
  ) INTO @sql
FROM extras;

SET @sql 
  = CONCAT('SELECT o.id,
                o.customer, ', @sql, ' 
            from orders o
            left join order_extras oe
              on o.id = oe.order_id
            left join extras e
              on oe.extra_id = e.id
            group by o.id, o.customer');

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

See SQL Fiddle with Demo.

参见SQL小提琴演示。

Both of these versions will give the result:

这两个版本都将给出结果:

| ID |   CUSTOMER | INSURANCE | LIFETIME UPDATES | PHONE SUPPORT |
------------------------------------------------------------------
|  1 | John Smith |     59.95 |                0 |             0 |
|  2 | Bob Newbie |         0 |                0 |             0 |
|  3 |  Bill Jobs |     59.95 |                0 |        124.95 |
|  4 | Ray Stantz |         0 |            79.95 |             0 |

#2

try this .

试试这个。

    select o.id , o.customer ,
    max(if(e.name = 'Insurance' , round(e.price,2), 0)) as Insurance,
    max(if(e.name = 'Lifetime Updates' , round(e.price,2), 0)) as Lifetime_Updates,
    max(if(e.name = 'Phone Support' , round(e.price,2), 0)) as Phone_Support

     from orders o
     left join order_extras oe
     on o.id = oe.order_id
    left join Extras e
    on oe.extra_id = e.id

    group by o.id, o.customer

DEMO HERE

演示

OUTPUT IS:

输出是:

   ID   CUSTOMER    INSURANCE   LIFETIME_UPDATES    PHONE_SUPPORT
    1   John Smith   59.95              0                0
    2   Bob Newbie     0                0                0
    3   Bill Jobs    59.95              0              124.95
    4   Ray Stantz     0              79.95              0