我知道LEAD 和 LAG 函式可以實作獲取上一個和下一個資料行的任務。我的問題是如何在不存在 LEAD & LAG 的舊 sql server 中實作相同的目標。請建議一些使用LEAD & LAG 函式檢索上一個和下一個資料行的好方法。謝謝
Here i am sharing one example which is working fine.
Create table #test
(
ID int identity(1,1),
Quarter nvarchar(20)
)
insert into #test values
('1Q 2010'),
('2Q 2010'),
('3Q 2010'),
('4Q 2010'),
('FY 2010')
select * from #test
select PrevID,PrevQuarter,CurrID,CurrQuarter
from
(
select Lag(ID,1) over(order by ID)PrevID ,LAG(Quarter,1)over(order by ID)PrevQuarter,
ID as CurrID,Quarter as CurrQuarter
from #test
)t
where CurrID=4
uj5u.com熱心網友回復：
您可以使用相關TOP子查詢代替LEADand LAG：
SELECT PrevID, PrevQuarter, CurrID, CurrQuarter
FROM
(
SELECT
(SELECT TOP 1 ID FROM #test t2
WHERE t2.ID < t1.ID ORDER BY t2.ID DESC) PrevID,
(SELECT TOP 1 Quarter FROM #test t2
WHERE t2.ID < t1.ID ORDER BY t2.ID DESC) PrevQuarter,
ID AS CurrID, Quarter AS CurrQuarter
FROM #test t1
) t
WHERE CurrID = 4;
這是一個作業演示。
uj5u.com熱心網友回復：
有很多方法可以做到這一點。這里有 2
自加入：
如果 ID 不連續，使用 row_number() 生成一個
select p.ID as PrevID, p.Quarter as PrevQuarter,
c.ID as CurrID, c.Quarter as CurrQuarter
from #test c
left join #test p on c.ID = p.ID 1
where c.ID = 4
使用APPLY()：
select p.ID as PrevID, p.Quarter as PrevQuarter,
c.ID as CurrID, c.Quarter as CurrQuarter
from #test c
outer apply
(
select top 1 ID, Quarter
from #test x
where x.ID < c.ID
order by x.ID desc
) p
where c.ID = 4
轉載請註明出處，本文鏈接：https://www.uj5u.com/qita/460689.html
標籤：tsql
上一篇：如何根據行在表頭表中設定標志
下一篇：混淆案例陳述值