當我在 Oracle DB 中執行查詢時，它回傳 0 行。如果沒有選擇行，我需要將列值回傳為 0。請查找隨附的示例查詢。
select NVL(staff,0), NVL(code,0)
from stafftable
where staffid= 123
and code in (112, 251)
and closedate is not null
and rownum<2
order by opendate desc;
如果沒有選擇行，我需要以下結果：
NVL(staff,0) NVL(code,0)
0 0
我試過 NVL()，coalsce() 不能得到預期的結果。
uj5u.com熱心網友回復：
我試過 NVL()，coalsce() 不能得到預期的結果。
因為它真的沒有回傳任何結果，所以我們可能會得到空結果
我們可以嘗試使用CTEwithUNION ALL
一個用于過濾結果，另一個用于空結果的默認結果
WITH cte AS (
select staff, code
from stafftable
where staffid= 123
and code in (112, 251)
and closedate is not null
and rownum<2
)
SELECT staff, code
FROM cte
UNION ALL
SELECT 0 , 0
FROM dual
WHERE NOT EXISTS (
SELECT 1
FROM cte
)
uj5u.com熱心網友回復：
您可以UNION ALL在一行中使用零，然后從 Oracle 12 開始FETCH FIRST ROW ONLY：
SELECT NVL(staff,0) AS staff,
NVL(code,0) AS code
FROM stafftable
WHERE staffid= 123
AND code in (112, 251)
AND closedate is not null
UNION ALL
SELECT 0, 0 FROM DUAL
-- ORDER BY staff DESC, code DESC
FETCH FIRST ROW ONLY;
這只會對stafftable.
如果您想在 Oracle 11 中執行相同操作，請使用子查詢并過濾外部查詢ROWNUM：
SELECT staff,
code
FROM (
SELECT NVL(staff,0) AS staff,
NVL(code,0) AS code
FROM stafftable
WHERE staffid= 123
AND code in (112, 251)
AND closedate is not null
UNION ALL
SELECT 0, 0 FROM DUAL
-- ORDER BY staff DESC, code DESC
)
WHERE ROWNUM = 1;
如果您想在OPENDATE那時訂購行：
SELECT *
FROM (
SELECT NVL(staff,0) AS staff,
NVL(code,0) AS code
FROM stafftable
WHERE staffid= 123
AND code in (112, 251)
AND closedate is not null
ORDER BY opendate DESC
)
UNION ALL
SELECT 0, 0 FROM DUAL
FETCH FIRST ROW ONLY;
或者
SELECT *
FROM (
SELECT *
FROM (
SELECT NVL(staff,0) AS staff,
NVL(code,0) AS code
FROM stafftable
WHERE staffid= 123
AND code in (112, 251)
AND closedate is not null
ORDER BY opendate DESC
)
UNION ALL
SELECT 0, 0 FROM DUAL
)
WHERE ROWNUM = 1;
db<>在這里擺弄
uj5u.com熱心網友回復：
另一個最大的解決方案，假設員工的值為正：
select max(staff) staff, max(code) code
FROM (
select staff, code
from stafftable
where staffid= 123
and code in (112, 251)
and closedate is not null
and rownum<2
UNION ALL
SELECT 0 , 0 FROM dual
)
轉載請註明出處，本文鏈接：https://www.uj5u.com/ruanti/466321.html
標籤：甲骨文