SQL select 按行增加运行总计列的数量

发布于 2024-09-02 06:38:34 字数 470 浏览 9 评论 0原文

假设我有一个包含列（DayId、RunningTotal）的表：

DayId    RunningTotal
---------------------
1        25
3        50
6        100
9        200
10       250

如何选择 DayId 以及 RunningTotal 较前一天增加的金额？即我如何选择：

DayId    DayTotal
---------------------
1        25
3        25
6        50
9        100
10       50

我知道的唯一当前方法是使用我试图分解的 while 循环。另外，DayId 没有规则的规则，只是它是一些递增的整数值，但它的增长量不规则，如示例表所示。

编辑：使用 MS SQL Server 2005

原文

Suppose I have a table with columns (DayId, RunningTotal):

DayId    RunningTotal
---------------------
1        25
3        50
6        100
9        200
10       250

How can I select the DayId and the amount the RunningTotal has increased from the previous day? i.e. how can I select:

DayId    DayTotal
---------------------
1        25
3        25
6        50
9        100
10       50

The only current method I know is with a while loop I am trying to factor out. Also, the DayId has no regular rules, just that it is some increasing integer value, but it increases by an irregular amount as shown in the example table.

EDIT: using MS SQL Server 2005

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

自找没趣 2024-09-09 06:38:34

with cte as (
  select dayid, runningtotal, row_number() over (order by dayid asc) as row_index
  from #the_table
)
select cur.dayid, cur.runningtotal - coalesce(prev.runningtotal, 0) as daytotal
from cte cur
     left join cte prev on prev.row_index = cur.row_index - 1

（我真的希望他们能够在 SQL Server 中实现对 lead 和 lag 函数的支持:|）

with cte as (
  select dayid, runningtotal, row_number() over (order by dayid asc) as row_index
  from #the_table
)
select cur.dayid, cur.runningtotal - coalesce(prev.runningtotal, 0) as daytotal
from cte cur
     left join cte prev on prev.row_index = cur.row_index - 1

(I really wish they'd implemented support for the lead and lag functions in SQL Server :|)

回复收藏 0 原文

凉墨 2024-09-09 06:38:34

可能有比这更简洁的方法，但请尝试：

select t3.DayId, 
    case when t4.DayId is null then t3.RunningTotal else t3.RunningTotal - t4.RunningTotal end as DayTotal
from (
    select t1.DayId, max(t2.DayId) as PreviousDayId as 
    from MyTable t1
    left outer join MyTable t2 on t2.DayId < t1.DayId
    group by t1.DayId    
) a
inner join MyTable t3 on a.DayId = t3.DayId
left outer join MyTable t4 on a.PreviousDayId = t4.DayId

There is probably a more succinct way than this, but try:

select t3.DayId, 
    case when t4.DayId is null then t3.RunningTotal else t3.RunningTotal - t4.RunningTotal end as DayTotal
from (
    select t1.DayId, max(t2.DayId) as PreviousDayId as 
    from MyTable t1
    left outer join MyTable t2 on t2.DayId < t1.DayId
    group by t1.DayId    
) a
inner join MyTable t3 on a.DayId = t3.DayId
left outer join MyTable t4 on a.PreviousDayId = t4.DayId

回复收藏 0 原文

~没有更多了~