当前位置: 动力学知识库 > 问答 > 编程问答 >

sql - How to populate cumulative max records from the table without using OLAP function?

问题描述:

I have a table like below

Id startdate enddate rate

1 1/1/2015 2/1/2015 10

1 2/1/2015 3/1/2015 15

1 3/1/2015 4/1/2015 5

1 4/1/2015 5/1/2015 10

1 5/1/2015 6/1/2015 20

1 6/1/2015 7/1/2015 30

1 7/1/2015 8/1/2015 10

1 8/1/2015 9/1/2015 30

1 9/1/2015 12/31/2015 20

I need to populate cumulative max values for each id (Id=1 for this example) including the first record, like below (SQL server 2008):

Id startdate enddate rate

1 1/1/2015 2/1/2015 10

1 2/1/2015 3/1/2015 15

1 5/1/2015 6/1/2015 20

1 6/1/2015 7/1/2015 30

1 8/1/2015 9/1/2015 30

Can any one help me on this?

网友答案:

You want to get the sequence of records with the max value at any given time. You can do this by calculating the cumulative max and then comparing to the original value. In SQL Server 2008, this can be accomplished with outer apply.

Here is how to express the query:

  select t.*
  from likebelow t outer apply
       (select top 1 max(rate) as maxrate
        from likebelow t2
        where t2.id < t.id
       ) tmax
  where tmax.maxrate is null or tmax.maxrate < t.rate;
分享给朋友:
您可能感兴趣的文章:
随机阅读: