基于来自另一列的一组值对列的值进行求和 [英] Sum the values of a column based on a group of values from another column

问题描述

我有一个表，其中每个交易与给定的价格相关联。像这样：

I have a table where each transaction is associated to a given price. Something like this:

transaction  price  
     1        10  
     2        20  
     3        30  
     5        50  
     6        10  
    10        10  
    23        10  
    24        10  
    25        10  
    26        10  
    27        10  

我试图找到一种方法来总计基于特定交易或一组交易。查询结果如下：

I'm trying to find a way to sum the price based on a specific transaction or for a group of transactions. The result of the query would be something like this:

transaction  price
   1-3        60
   5-6        60
    10        10
 23-27        50

告诉交易1到3的结果是60，等等。

This way I can tell that for the transactions 1 to 3 the result was 60, and so on. Can you point me in the right direction to make this using MySQL?

推荐答案

由于mysql不支持窗口函数，我们必须为您的表创建自己的组排名，然后对结果进行另一个查询操作。

Since mysql doesn't have support for windowing functions, we have to create our own group ranking for your table, and then another query to operate on the results.

select if(count(transaction) = 1, transaction, concat(min(transaction), '-', max(transaction))) transactions, sum(price) price 
  from (
    select if(`transaction` = @prev + 1, 
         if(@prev := `transaction`, @rank, @rank),
           if(@prev := `transaction`, @rank := @rank + 1, @rank := @rank + 1)
       ) gr,
       `transaction`,
       price
    from table1, (select @rank := 1, @prev := 0) q
    order by `transaction` asc
  ) q     
  group by gr

这里演示

这篇关于基于来自另一列的一组值对列的值进行求和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！