I have a table below
我下面有一张桌子
first second
------- ----------
100 0
200 0
0 400
I want to get below result
我想要低于结果
first second result
------- ---------- ----------
100 0 100
200 0 300
0 400 -100
As you can see that result parameter is sum of previous (first-sum) How can i write such a query ?
正如您所看到的,结果参数是先前(第一和)的和,我如何编写这样的查询?
MYSQL solution is very simple, but simple solutions are looking for Microsoft Sql Server.
MYSQL解决方案非常简单,但是简单的解决方案正在寻找Microsoft Sql Server。
set @result =0;
select first, second, @result := @result + first - second as result
from tablo;
results
结果
first second result
100 0 100
200 0 300
0 400 -100
2 个解决方案
#1
3
Here's a version with a common table expression. It also suffers from the lack-of-ordering issue, so I used second, first to get the desired results.
这里有一个具有公共表表达式的版本。它还存在订购不足的问题,因此我使用了second, first来获得所需的结果。
WITH cte as
(
select [first], [second], [first] - [second] as result,
ROW_NUMBER() OVER (ORDER BY second, first) AS sequence
from tableo
)
SELECT t.[first], t.[second], SUM(t2.result) AS result
from cte t
JOIN cte t2 on t.sequence >= t2.sequence
GROUP BY t.[first], t.[second]
#2
4
Your first problem is that you're assuming order where there is none. A query without an order by clause has no guaranteed order. Tables without a clustered index don't have a defined order.
你的第一个问题是你在没有秩序的地方假设秩序。没有order by子句的查询没有保证的顺序。没有聚集索引的表没有定义的顺序。
So, if we fix that and put an identity
column on the table so that we do have a well defined order, you can use a recursive CTE do it (in mssql 2005 and newer):
因此,如果我们将其固定并在表上放置一个标识列,这样我们就有了一个定义良好的顺序,您可以使用递归CTE(在mssql 2005和更新版本中):
with running_sum as (
select
t.id, t.first, t.second, t.first-t.second as result
from
table t where t.id = 1
UNION ALL
select
t.id, t.first, t.second, r.result+t.first-t.second
from
table t
join running_sum r on r.id = t.id - 1
)
select
*
from
running_sum
order by
id
#1
3
Here's a version with a common table expression. It also suffers from the lack-of-ordering issue, so I used second, first to get the desired results.
这里有一个具有公共表表达式的版本。它还存在订购不足的问题,因此我使用了second, first来获得所需的结果。
WITH cte as
(
select [first], [second], [first] - [second] as result,
ROW_NUMBER() OVER (ORDER BY second, first) AS sequence
from tableo
)
SELECT t.[first], t.[second], SUM(t2.result) AS result
from cte t
JOIN cte t2 on t.sequence >= t2.sequence
GROUP BY t.[first], t.[second]
#2
4
Your first problem is that you're assuming order where there is none. A query without an order by clause has no guaranteed order. Tables without a clustered index don't have a defined order.
你的第一个问题是你在没有秩序的地方假设秩序。没有order by子句的查询没有保证的顺序。没有聚集索引的表没有定义的顺序。
So, if we fix that and put an identity
column on the table so that we do have a well defined order, you can use a recursive CTE do it (in mssql 2005 and newer):
因此,如果我们将其固定并在表上放置一个标识列,这样我们就有了一个定义良好的顺序,您可以使用递归CTE(在mssql 2005和更新版本中):
with running_sum as (
select
t.id, t.first, t.second, t.first-t.second as result
from
table t where t.id = 1
UNION ALL
select
t.id, t.first, t.second, r.result+t.first-t.second
from
table t
join running_sum r on r.id = t.id - 1
)
select
*
from
running_sum
order by
id