MySQL SELECT函数汇总当前数据 [英] MySQL SELECT function to sum current data

问题描述

我有一个查询，返回的内容如下:

I have a query that return something like this:

| ID | Val |  
| 0  | 10  |     
| 1  | 20  |     
| 2  | 30  |  

但除此之外，我想要这样的东西:

But instead of that, I want something like this:

| ID | Val | Sum |   
| 0  | 10  |  10 |   
| 1  | 20  |  30 |   
| 2  | 30  |  60 |   

这是在查询中执行此操作的一种方法(我正在使用MySQL)?

Is that a way to do it on the query (I'm using MySQL)?

Tks

推荐答案

这称为累积总和.

在Oracle和PostgreSQL中，它是使用窗口函数计算的:

In Oracle and PostgreSQL, it is calculated using a window function:

SELECT  id, val, SUM() OVER (ORDER BY id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
FROM    mytable

但是，MySQL不支持它.

在MySQL中，您可以使用会话变量进行计算:

In MySQL, you can calculate it using session variables:

SET @s = 0;

SELECT  id, val, @s := @s + val
FROM    mytable
ORDER BY
        id
;

或以纯粹的基于集合但效率较低的方式:

or in a pure set-based but less efficient way:

SELECT  t1.id, t1.val, SUM(t2.val)
FROM    mytable t1
JOIN    mytable t2
ON      t2.id <= t1.id
GROUP BY
        t1.id
;

这篇关于MySQL SELECT函数汇总当前数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！