mysql - 加入多行并使用 MySQL(和 PHP)对它们求和

Question

我有一个类似这样的表结构:

    id | order1_id | order1_type | order1_amount | order2_id | order2_type | order2_amount
------------------------------------------------------------------------------------------
    1        1           3             4                 1         4              5
    2        2           1             1                 1         3              2
    3        1           4             4                 2         2              1

我想这样获取数据:

订单编号 |订单类型 |订单金额

1          3            6
1          4            9
2          1            1
2          2            1

我想按类型分组，然后对订单金额求和。我该怎么做？

谢谢，

Answer 1

我将在子查询中使用联合来排列列，然后对其进行分组和求和。

假设您受困于这种不太理想的表结构，您可能想要创建一个表示下面子查询的 View ，然后针对该 View 运行 group by/sum。我猜想这样的 View 可能在更多地方有用，而不仅仅是这个查询。

select t.order_id, t.order_type, sum(t.order_amount)
from (select order1_id as order_id, order1_type as order_type, order1_amount as order_amount
      from orders
      union all
      select order2_id as order_id, order2_type as order_type, order2_amount as order_amount
      from orders  
      union all        
      select order3_id as order_id, order3_type as order_type, order3_amount as order_amount
      from orders 
      union all         
      select order4_id as order_id, order4_type as order_type, order4_amount as order_amount
      from orders    
      union all      
      select order5_id as order_id, order5_type as order_type, order5_amount as order_amount
      from orders) t
group by t.order_id, t.order_type