问题描述:

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;
相关阅读:
Top