返回每一行的行组和

Can the sum of column values for rows meeting selection criteria be returned not only with each individual row but for multiple groups meeting the same criteria?

满足选择条件的行的列值之和是否不仅可以对每一行返回，还可以对满足相同条件的多个组返回?

For example, consider:

例如,考虑:

id  order_id    quantity
1   1           1
2   1           3
3   1           5
4   2           2
5   2           4

What MySQL SELECT query could return the follow results:

MySQL SELECT查询可以返回以下结果:

id  order_id    quantity    order_sum
1   1           1           9
2   1           3           9
3   1           5           9
4   2           2           6
5   2           4           6

EDIT: follow-up question: assuming the results are produced from a query with multiple conditions, how would these conditions be handled in the final query?

编辑:后续问题:假设结果是由具有多个条件的查询生成的，那么在最终查询中如何处理这些条件?

3 个解决方案

#1

You can use a subquery in the "from" clause:

您可以在“from”子句中使用子查询:

select  t.*, tsum.sumquantity
from t join
     (select t.orderid, sum(quantity) as sumquantity
      from t
      group by t.order_id
     ) tsum
     on t.orderid = tsum.orderid

The advantage of putting it in the "from" clause is that you can have multiple variables. For instance, you could add the number of orders, the maximum quantity, the number of times exactly 1 quantity appears, and so on.

将它放入“from”子句的好处是可以有多个变量。例如，您可以添加订单的数量，最大数量，恰好出现一个数量的次数，等等。

#2

You could use a subquery:

您可以使用子查询:

select  *
,       (
        select  sum(quantity)
        from    YourTable yt2
        where   yt2.order_id = yt1.order_id
        ) as order_sum
from    YourTable yt1

#3

you need to have a subquery that will compute the SUM of the quantity for each Order_ID

您需要有一个子查询来计算每个Order_ID的数量之和

SELECT  a.*, b.order_sum
FROM    myTable a INNER JOIN
            (
                SELECT order_ID, 
                       SUM(quantity) order_sum
                FROM    myTable
                GROUP BY order_ID
            ) b on a.order_ID = b.order_ID

#1