我正在計算一個運行余額，并希望將所有行標記為 0 值以具有與帳戶 ID 相關的“匹配”標志，否則為“不匹配”標志。
這是我嘗試過但沒有得到正確結果的方法：
SEL a.*,
CASE WHEN RUNNING_BALANCE OVER (PARTITION BY ACCT_SROGT_ID ROWS UNBOUNDED PRECEDING ) = 0 THEN 'M' ELSE 'NM' END R
FROM NON_MATCHING_RUNNING_BALANCE a
uj5u.com熱心網友回復：
我們可以使用子查詢找到最后一個為 0 的 acct_rank，然后用用例測驗每一行。
Select
a.*,
Case when a.acct_rank > z.last_zero
Then 'unmatched' else 'matched'
End as is_matched
From accounts a
Join ( select
account_id as id,
MAX(acct_rank) last_zero
From accounts
Where running_balance = 0
Group by account_id) z
On a.account_id = z.id;
uj5u.com熱心網友回復：
檢查這個
WITH SUB AS (select NON_MATCHING_RUNNING_BALANCE. ,row_number()over(partition by [Account ID] order by [Account ID]) RN from NON_MATCHING_RUNNING_BALANCE) SELECT SUB. ,CASE WHEN SUB.RN<=SUB2.RN AND SUB.[Account ID]=SUB2.[Account ID] THEN 'MATCHED' ELSE 'Not-Matched' END AS 'MATCH' FROM SUB LEFT JOIN (SELECT * FROM SUB WHERE value=0 ) SUB2 ON SUB.[帳戶 ID]=SUB2.[帳戶 ID]
轉載請註明出處，本文鏈接：https://www.uj5u.com/net/458869.html
下一篇：比AND運算子有效的查詢