我試圖找到一種方法來創建一個查詢，該查詢突出顯示預期交易是否在 SQL Server 2016 中跟隨付款。
我的資料大概是
SELECT 123456 AS Casekey
, 1 as rnk
, NULL AS LAPayment
, 1080.00 AS LAReserve
UNION ALL
SELECT 123456 AS Casekey
, 2 as rnk
, 1080.00 AS LAPayment
, null AS LAReserve
UNION ALL
SELECT 123456 AS Casekey
, 3 as rnk
, NULL AS LAPayment
, NULL AS LAReserve
在 LAPayment 之后應該有一個 0 的后續 LAReserve 但是沒有 - 我需要在發生這種情況時強調 - 基本上付款將準備金減少到 0 但系統配置的方式 0 被輸入到交易螢屏和資料必須反映系統 - 遺憾的是，這超出了我的控制范圍！
uj5u.com熱心網友回復：
您可以LEAD為此使用視窗函式
SELECT *
FROM (
SELECT *,
NextLAReserve = LEAD(LAReserve) OVER (PARTITION BY Casekey ORDER BY rnk)
FROM YourTable t
) t
WHERE t.LAPayment > 0 AND t.NextLAReserve IS NULL;
uj5u.com熱心網友回復：
Windows 函式在這方面很有用。在此問題中，您可能需要 LAG() 或 LEAD()。以上@Charlieface 的答案給出了 LEAD()，這給出了 LAG()
LAG() 視窗函式可用于這種情況。最好給出預期輸出，以便在發布前測驗結果。
SELECT Casekey
, rnk
, LAPayment
, CASE WHEN LAG(LAPayment) over (order by Casekey) is not null then 0 ELSE LAReserve END AS LAReserve
FROM
(SELECT 123456 AS Casekey
, 1 as rnk
, NULL AS LAPayment
, 1080.00 AS LAReserve
UNION ALL
SELECT 123456 AS Casekey
, 2 as rnk
, 1080.00 AS LAPayment
, null AS LAReserve
UNION ALL
SELECT 123456 AS Casekey
, 3 as rnk
, NULL AS LAPayment
, NULL AS LAReserve
) AS A
轉載請註明出處，本文鏈接：https://www.uj5u.com/qukuanlian/313298.html
上一篇：基于SQLServer存盤程序的引數優化過濾器的最有效方法是什么？
下一篇：在展開的欄位上連接表