我正在使用 Azure SQL。
我想生成一個包含 30 列的查找報告，在其中檢查列在這段時間內的演變，但僅限于當天而不是所有列。每列代表過去 1、2、3、... 30 天的價格。
如果我在當前日期應用 where 子句，則 lag 函式不會回溯任何日子。
這是它目前的樣子：
這是它應該看起來的樣子：
我嘗試的查詢：
select
value, date,
LAG(value,1) OVER ( partition by name, adress ORDER by date)AS day_1,
LAG(value,2) OVER ( partition by name, adress ORDER by date)AS day_2,
LAG(value,3) OVER ( partition by name, adress ORDER by date)AS day_3,
LAG(value,4) OVER ( partition by name, adress ORDER by date)AS day_4,
LAG(value,5) OVER ( partition by name, adress ORDER by date)AS day_5,
LAG(value,6) OVER ( partition by name, adress ORDER by date)AS day_6
from
products where date = cast(GETDATE() as Date)
uj5u.com熱心網友回復：
嘗試這個：
WITH CTE1 AS
(
select
value, date,
LAG(value,1) OVER ( partition by name, adress ORDER by date)AS day_1,
LAG(value,2) OVER ( partition by name, adress ORDER by date)AS day_2,
LAG(value,3) OVER ( partition by name, adress ORDER by date)AS day_3,
LAG(value,4) OVER ( partition by name, adress ORDER by date)AS day_4,
LAG(value,5) OVER ( partition by name, adress ORDER by date)AS day_5,
LAG(value,6) OVER ( partition by name, adress ORDER by date)AS day_6
from products
)
SELECT *
FROM CTE1
WHERE date = cast(GETDATE() as Date);
您面臨的問題是，一旦您WHERE為當前日期過濾器添加子句，資料就會被過濾，然后LAG函式將應用于僅具有當前日期的行（因為其他日期資料已被過濾掉）。
所以解決方案是首先應用LAG函式，然后WHERE對從上一個查詢中獲得的結果應用子句。
轉載請註明出處，本文鏈接：https://www.uj5u.com/net/487281.html