关于查询一个递增累计值的断点问题
id name date value
129069本期累计20120730
129069本期累计20120629
129069本期累计20120527
129069本期累计20120435
129069本期累计20120226
129069本期累计20120124
129069本期累计20111150
129069本期累计20111030
129069本期累计20110925
129069本期累计20110724
129069本期累计20110625
129069本期累计20100630
129069本期累计20100515
129069本期累计20100413
求教大手，按照年份2012,2011,2010的不同，累计值按月份的增加应该是递增的，如何查询出错误的数据即递增数据中的断点？
即返回
id name date value
129069本期累计20120435
129069本期累计20110625
建表语句如下：
create table #test(id int,name varchar(9),date int,value int)
insert #test
select 129069,'本期累计',201207,30 union all
select 129069,'本期累计',201206,29 union all
select 129069,'本期累计',201205,27 union all
select 129069,'本期累计',201204,35 union all
select 129069,'本期累计',201202,26 union all
select 129069,'本期累计',201201,24 union all
select 129069,'本期累计',201111,50 union all
select 129069,'本期累计',201110,30 union all
select 129069,'本期累计',201109,25 union all
select 129069,'本期累计',201107,24 union all
select 129069,'本期累计',201106,25 union all
select 129069,'本期累计',201006,30 union all
select 129069,'本期累计',201005,15 union all
select 129069,'本期累计',201004,13
go

[解决办法]

SQL code

create table #test(id int,name varchar(9),date int,value int)insert #testselect 129069,'本期累计',201207,30 union allselect 129069,'本期累计',201206,29 union allselect 129069,'本期累计',201205,27 union allselect 129069,'本期累计',201204,35 union allselect 129069,'本期累计',201202,26 union allselect 129069,'本期累计',201201,24 union allselect 129069,'本期累计',201111,50 union allselect 129069,'本期累计',201110,30 union allselect 129069,'本期累计',201109,25 union allselect 129069,'本期累计',201107,24 union allselect 129069,'本期累计',201106,25 union allselect 129069,'本期累计',201006,30 union allselect 129069,'本期累计',201005,15 union allselect 129069,'本期累计',201004,13go--方法1;WITH t AS(    SELECT ROW_NUMBER() OVER(PARTITION BY LEFT([date],4) ORDER BY [date]) AS GroupID        ,LEFT([date],4) AS YY        ,*     FROM #test)SELECT o.id,o.name,o.date,o.value FROM t o WHERE exists(SELECT * FROM t i WHERE i.YY = o.YY and i.GroupID-1 = o.GroupID and i.value < o.value)--方法2;WITH t AS(    SELECT ROW_NUMBER() OVER(PARTITION BY LEFT([date],4) ORDER BY [date]) AS GroupID        ,LEFT([date],4) AS YY        ,*     FROM #test)SELECT a.id,a.name,a.date,a.value FROM t a left join t b ON a.YY = b.YY and a.GroupID = b.GroupID-1 and a.value > b.valueWHERE b.id is not null/*id          name      date        value----------- --------- ----------- -----------129069      本期累计      201106      25129069      本期累计      201204      35(2 row(s) affected)*/