SQL Server How to use LEAD and LAG In Where?

nhjlsmyf 于 2023-11-16 发布在其他

关注(0)|答案(2)|浏览(106)

i have a table like this:

Number   Price    Type       Date         Time
------   -----    ----    ----------    ---------
23456    0,665     SV     2014/02/02     08:00:02
23457    1,3       EC     2014/02/02     07:50:45
23460    0,668     SV     2014/02/02     07:36:34
23461    0,668     SV     2014/02/02     07:37:34
23462    0,668     SV     2014/02/02     07:38:34
23463    0,668     SV     2014/02/02     07:39:34

For each record I need previous/next price. In this case, the query is simple.

Select Lag(price) over (order by date desc, time desc),
Lead(price) over (order by date desc, time desc)
from ITEMS

but i need the result Where Next price <> record price

My Query is

Select Lag(price) over (order by date desc, time desc) Nxt_Price,
    Lead(price) over (order by date desc, time desc) Prv_Price
    from ITEMS
 Where Nxt_Price <> price

but it refused to use that variable

sql-server

来源：https://stackoverflow.com/questions/26864930/how-to-use-lead-and-lag-in-where

2条答案

按热度按时间

bweufnob1#

Try the below query:

SELECT Nxt_Price, Prv_Price 
FROM
    (Select price, Lag(price) over (order by date desc, time desc) Nxt_Price,
    Lead(price) over (order by date desc, time desc) Prv_Price
    from ITEMS) AS InnerQuery
 Where Nxt_Price <> price

It may help you.

赞(0）回复(0）举报 2023-11-16

xoefb8l82#

Use a common table expression.

with myItems as (
    select
       Lag(price) over (order by date desc, time desc) Nxt_Price,
       Lead(price) over (order by date desc, time desc) Prv_Price
    from ITEMS
)
select *
from myItems
where Nxt_Price <> Prv_Price

赞(0）回复(0）举报 2023-11-16

我来回答

SQL Server How to use LEAD and LAG In Where?

2条答案

相关问题

热门标签

最新问答