sql求最近三条记录的最大值

[sjlsy]

表如下 tb

DATE1 JE MAX3
2015-02-01 1.1
2015-02-02 1.6
2015-02-04 1.3
2015-02-06 1.9
2015-02-07 1.7
2015-02-10 1.1
2015-02-12 1.5

设置MAX3值为DATE1大于等于当前DATE1的最近三条记录的最大JE,如:第一行MAX3=1.6,第二行MAX3=1.9
一条sql可行吗

[听雨停了]

引用 5 楼 sjlsy 的回复:

[quote=引用 2 楼 ayalicer 的回复:]
正常人 思路 最近三条 肯定死当天以及之前三天 而不是未来3条

我把问题改下,表如下 tb

ID JE MAX3
1 1.1
2 1.6
4 1.3
6 1.9
7 1.7
10 1.1
12 1.5

设置MAX3值为ID大于等于当前记录ID的最小三条记录的最大JE,如:第一行MAX3=1.6,第二行MAX3=1.9
一条sql可行吗[/quote]

--测试数据
IF Object_id('Tempdb.dbo.#T') is not null
     DROP TABLE #T
     
CREATE TABLE #T (
          List_ID int ,          
          Je decimal(13,2))
 
INSERT INTO #T
Select 1, 1.1 union 
Select 2,1.6 union
Select 4,1.3 union
Select 6,1.9 union 
Select 7,1.7 union
Select 10,1.1 union 
Select 12,1.5
--测试数据结束

;WITH cte AS (
         SELECT ROW_NUMBER() OVER(ORDER BY List_ID) AS rn,
                *
         FROM   #t
     )
SELECT *,
       (
           SELECT MAX(je)
           FROM   cte a
           WHERE  a.rn BETWEEN b.rn AND b.rn + 2	--三个比较就+2,四个就+3,以此类推...
       ) AS maxje
FROM   cte b

[sjlsy]

引用 7 楼 qq_37170555 的回复:

[quote=引用 5 楼 sjlsy 的回复:] [quote=引用 2 楼 ayalicer 的回复:] 正常人 思路 最近三条 肯定死当天以及之前三天 而不是未来3条

我把问题改下,表如下 tb ID JE MAX3 1 1.1 2 1.6 4 1.3 6 1.9 7 1.7 10 1.1 12 1.5 设置MAX3值为ID大于等于当前记录ID的最小三条记录的最大JE,如:第一行MAX3=1.6,第二行MAX3=1.9 一条sql可行吗[/quote]

--测试数据
IF Object_id('Tempdb.dbo.#T') is not null
     DROP TABLE #T
     
CREATE TABLE #T (
          List_ID int ,          
          Je decimal(13,2))
 
INSERT INTO #T
Select 1, 1.1 union 
Select 2,1.6 union
Select 4,1.3 union
Select 6,1.9 union 
Select 7,1.7 union
Select 10,1.1 union 
Select 12,1.5
--测试数据结束

;WITH cte AS (
         SELECT ROW_NUMBER() OVER(ORDER BY List_ID) AS rn,
                *
         FROM   #t
     )
SELECT *,
       (
           SELECT MAX(je)
           FROM   cte a
           WHERE  a.rn BETWEEN b.rn AND b.rn + 2	--三个比较就+2,四个就+3,以此类推...
       ) AS maxje
FROM   cte b

[/quote] 非常感谢各位,你们结果是正确的, 我自已搞定了,我用的是mysql update tb left join (select a.id,max(b.je) as maxJE from tb a left join tb b on b.id>=a.id and b.id<a.id+3 group by a.id)t2 on tb.id=t2.id set tb.max3=t2.maxje sqlserver应该这样: update tb set tb.max3=t2.maxje from tb left join (select a.id,max(b.je) as maxJE from tb a left join tb b on b.id>=a.id and b.id<a.id+3 group by a.id)t2 on tb.id=t2.id 我在想能不能不加list_id,和ROW_NUMBER()...........................................................................

[砸死牛顿的苹果]

主要看最近三条怎么定义

[砸死牛顿的苹果]

正常人 思路 最近三条 肯定死当天以及之前三天 而不是未来3条

[sjlsy]

引用 1 楼 ayalicer 的回复:

主要看最近三条怎么定义

如当前DATA1值是"2015-02-02" 则最近三条是 select top 3 * from tb where DATA1>"2015-02-02" order by DATA1

[顺势而为1]

是这样吗

IF not Object_id('Tempdb..#Tmp_Data') is null
     DROP TABLE #Tmp_Data
   
CREATE TABLE #Tmp_Data (
          List_ID int identity(1,1),
          Date1 datetime,
		  Je decimal(13,2))
		  
INSERT INTO #Tmp_Data
Select '2015-02-01', 1.1 union 
Select '2015-02-02',       1.6 union
Select '2015-02-04',       1.3 union
Select '2015-02-06',       1.9 union 
Select '2015-02-07',       1.7 union
Select '2015-02-10',       1.1 union 
Select '2015-02-12',       1.5

Select *,(case when List_ID<=(Select max(List_ID)-2 From #Tmp_Data) then (Select max(je) From #Tmp_Data Where List_ID<=a.List_ID+2)
               when List_ID<=(Select max(List_ID)-1 From #Tmp_Data) then (Select max(je) From #Tmp_Data Where List_ID Between a.List_ID-1 and a.List_ID+1)
			   else (Select max(je) From #Tmp_Data Where List_ID>=a.List_ID-2) 
			   end )
From #Tmp_Data a 

List_ID     Date1                   Je                                      
----------- ----------------------- --------------------------------------- ---------------------------------------
1           2015-02-01 00:00:00.000 1.10                                    1.60
2           2015-02-02 00:00:00.000 1.60                                    1.90
3           2015-02-04 00:00:00.000 1.30                                    1.90
4           2015-02-06 00:00:00.000 1.90                                    1.90
5           2015-02-07 00:00:00.000 1.70                                    1.90
6           2015-02-10 00:00:00.000 1.10                                    1.70
7           2015-02-12 00:00:00.000 1.50                                    1.70

(7 row(s) affected)
     

[sjlsy]

引用 2 楼 ayalicer 的回复:

正常人 思路 最近三条 肯定死当天以及之前三天 而不是未来3条

我把问题改下,表如下 tb ID JE MAX3 1 1.1 2 1.6 4 1.3 6 1.9 7 1.7 10 1.1 12 1.5 设置MAX3值为ID大于等于当前记录ID的最小三条记录的最大JE,如:第一行MAX3=1.6,第二行MAX3=1.9 一条sql可行吗

[砸死牛顿的苹果]

IF Object_id('Tempdb.dbo.#T') is not null
     DROP TABLE #T
    
CREATE TABLE #T (
          List_ID int ,
          Date1 datetime,
          Je decimal(13,2))

INSERT INTO #T
Select 1,'2015-02-01', 1.1 union 
Select 2,'2015-02-02',       1.6 union
Select 4,'2015-02-04',       1.3 union
Select 6,'2015-02-06',       1.9 union 
Select 7,'2015-02-07',       1.7 union
Select 10,'2015-02-10',       1.1 union 
Select 12,'2015-02-12',       1.5

select *,(select max(je) from(select top 3 je from #t b where b.List_ID >=a.List_ID order by b.List_ID)t)
from #t a
/*
List_ID	Date1	Je	(无列名)
1	2015-02-01 00:00:00.000	1.10	1.60
2	2015-02-02 00:00:00.000	1.60	1.90
4	2015-02-04 00:00:00.000	1.30	1.90
6	2015-02-06 00:00:00.000	1.90	1.90
7	2015-02-07 00:00:00.000	1.70	1.70
10	2015-02-10 00:00:00.000	1.10	1.50
12	2015-02-12 00:00:00.000	1.50	1.50
*/