22,207
社区成员
发帖
与我相关
我的任务
分享
--> --> 中国风(Roy)生成測試數據
if not object_id(N'Tempdb..#1') is null
drop table #1
Go
Create table #1([Name] nvarchar(22),[Date] Date,[Val] int)
Insert #1
select N'W1','2011/01/01',20 union all
select N'W1','2011/01/02',200 union all
select N'W1','2011/01/03',25 union all
select N'W1','2011/01/04',100 union all
select N'W1','2011/01/05',20 union all
select N'W1','2011/01/06',15 union all
select N'W2','2011/01/01',10 union all
select N'W2','2011/01/02',15 union all
select N'W2','2011/01/03',200 union all
select N'W2','2011/01/04',100 union all
select N'W2','2011/01/05',20 union all
select N'W2','2011/01/06',150
Go
;with t as
(select name,date,val from #1 a
where not exists(select 1 from #1 b where a.Name =b.name and a.Date >b.Date)
)
,a as
(select *,1 ck from t
union all
select
a.name
,b.date
,case when (b.val-a.val)<10 then b.val else a.val end
,case when (b.val-a.val)<10 then 1 else 0 end
from a,#1 b
where b.Name =a.Name and b.Date =dateadd(d,1,a.date)
)
select name,max(val)maxV from a where ck=1 group by name
/*
name maxV
W1 25
W2 20
*/
use Tempdb
go
--> --> 中国风(Roy)生成測試數據
if not object_id(N'Tempdb..#1') is null
drop table #1
Go
Create table #1([Name] nvarchar(22),[Date] Date,[Val] int)
Insert #1
select N'W1','2011/01/01',10 union all
select N'W1','2011/01/02',200 union all
select N'W1','2011/01/03',15 union all
select N'W1','2011/01/04',100 union all
select N'W1','2011/01/05',20 union all
select N'W1','2011/01/06',15
Go
SELECT [Name],[Val]=MAX([Val])
FROM ( SELECT * ,
beforeVal = LAG(Val) OVER ( PARTITION BY Name ORDER BY Date ) --同一个Name前一个值
FROM #1
) AS T
WHERE Val - ISNULL(beforeVal, Val) < 10
GROUP BY [Name]
/*
Name Val
W1 20
*/