34,588
社区成员
发帖
与我相关
我的任务
分享
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
--方法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.value
WHERE b.id is not null
/*
id name date value
----------- --------- ----------- -----------
129069 本期累计 201106 25
129069 本期累计 201204 35
(2 row(s) affected)
*/