我有一個帶有嵌套 IF 條件的 excel 公式，該公式將當前行值與前一行和前一行與同一列的前第二行值進行比較。
公式：
=IF(B8<>B7,IF(B7<>B6,B6,B7),B8)
嘗試了以下查詢，但收到錯誤“ORA-00920：關系運算子無效”
select MYTABLE.*,
CASE WHEN MSYMBOL over (order by MDATE) <> lag(MSYMBOL,1) over (order by MDATE)
THEN CASE
WHEN lag(MSYMBOL,1) over (order by MDATE) <> lag(MSYMBOL,2) over (order by MDATE)
THEN lag(MSYMBOL,2) over (order by MDATE)
ELSE lag(MSYMBOL,1) over (order by MDATE)
END,
ELSE MSYMBOL over (order by MDATE)
END as FLAG
from MYTABLE
表創建、插入陳述句和查詢在此鏈接DB<>FIDDLE中更新。
感謝您對此的任何幫助。提前致謝。
謝謝，
里查
uj5u.com熱心網友回復：
MSYMBOL over (order by MDATE)無效，您只想使用MSYMBOL.- 在第一個
END.之后有一個額外的逗號。 - 此外，
NULL <> something永遠不是真的，需要考慮到這一點。
你可以使用：
select MYTABLE.*,
CASE
WHEN MSYMBOL <> lag(MSYMBOL,1) over (order by MDATE)
OR (MSYMBOL IS NULL AND lag(MSYMBOL,1) over (order by MDATE) IS NOT NULL)
OR (MSYMBOL IS NOT NULL AND lag(MSYMBOL,1) over (order by MDATE) IS NULL)
THEN CASE
WHEN lag(MSYMBOL,1) over (order by MDATE) <> lag(MSYMBOL,2) over (order by MDATE)
OR (lag(MSYMBOL,1) over (order by MDATE) IS NULL AND lag(MSYMBOL,2) over (order by MDATE) IS NOT NULL)
OR (lag(MSYMBOL,1) over (order by MDATE) IS NOT NULL AND lag(MSYMBOL,2) over (order by MDATE) IS NULL)
THEN lag(MSYMBOL,2) over (order by MDATE)
ELSE lag(MSYMBOL,1) over (order by MDATE)
END
ELSE MSYMBOL
END as FLAG
from MYTABLE
但是您可能會更好地反轉比較以使用=.
select MYTABLE.*,
CASE
WHEN MSYMBOL = lag(MSYMBOL,1) over (order by MDATE)
THEN MSYMBOL
WHEN lag(MSYMBOL,1) over (order by MDATE) = lag(MSYMBOL,2) over (order by MDATE)
THEN lag(MSYMBOL,1) over (order by MDATE)
ELSE lag(MSYMBOL,2) over (order by MDATE)
END as FLAG
from MYTABLE
哪些輸出：
日期 符號 旗幟 01-NOV-21 02-NOV-21 03-NOV-21 04-NOV-21 05-NOV-21 正方形 06-NOV-21 圓圈 07-NOV-21 圓圈 圓圈 08-NOV-21 圓圈 圓圈 21 年 11 月 9 日 正方形 圓圈
db<>在這里擺弄
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/367854.html
下一篇：有沒有辦法縮短這個查詢？