WITH T1 as ( select id,data,max(data)over(order by id) p_data from T ),T2 as ( select id,min(data)over(order by id desc) n_data from T ) select T1.ID,T1.DATA,T1.P_DATA,T2.N_DATA from T1,T2 WHERE T1.ID=T2.ID
SELECT ID,DATA,LAG(P_DATA)OVER(ORDER BY ID) p_data FROM ( select id,data,max(data)over(order by id) p_data from T )
另外,再问一下,如果当前记录前所有记录(不包括当前记录)中最大的该怎么写? [code=sql]WITH T1 as ( select id,data,max(data)over(order by id rows between unbounded preceding and 1 preceding) p_data from T ),T2 as ( select id,min(data)over(order by id desc) n_data from T ) select T1.ID,T1.DATA,T1.P_DATA,T2.N_DATA from T1,T2 WHERE T1.ID=T2.ID ORDER BY ENAME ROWS BETWEEN 1 PRECEDING AND CURRENT ROW
select id,data,max(data)over(order by id) p_data
from T
),T2 as (
select id,min(data)over(order by id desc) n_data
from T
)
select T1.ID,T1.DATA,T1.P_DATA,T2.N_DATA
from T1,T2
WHERE T1.ID=T2.ID
FROM (
select id,data,max(data)over(order by id) p_data
from T
)
另外,再问一下,如果当前记录前所有记录(不包括当前记录)中最大的该怎么写?
[code=sql]WITH T1 as (
select id,data,max(data)over(order by id rows between unbounded preceding and 1 preceding) p_data
from T
),T2 as (
select id,min(data)over(order by id desc) n_data
from T
)
select T1.ID,T1.DATA,T1.P_DATA,T2.N_DATA
from T1,T2
WHERE T1.ID=T2.ID
ORDER BY ENAME ROWS
BETWEEN 1 PRECEDING AND CURRENT ROW