CASE 語句 - 第一種情況被忽略

我有一個表 ( usr_t_user_reg)，我在其中註冊了數據庫的所有使用者。現在我想創建一個視圖 ( usr_v_user_not_reg)，它向我顯示我尚未註冊的所有使用者或不再在數據庫中的使用者。

該表如下所示：

| username | db_instance | forename | surname | ...... |
|----------|-------------|----------|---------|--------|
|  xxxxx   |    DB1      |   xxxxx  |   xxxx  | ...... |
|  xxxxx   |    DB1      |   xxxxx  |   xxxx  | ...... |
|  xxxxx   |    DB2      |   xxxxx  |   xxxx  | ...... |

這是創建視圖的語句：

CREATE OR REPLACE FORCE VIEW usr_v_user_not_reg AS 
SELECT username "User", db_instance "Instance",
(
 CASE 
   WHEN username IN
   (
     SELECT username 
     FROM SYS.dba_users
     MINUS
     SELECT username 
     FROM usr_t_user_reg
   ) THEN 'not registered'
   WHEN username IN
   (
     SELECT username 
     FROM usr_t_user_reg
     MINUS
     SELECT username 
     FROM SYS.dba_users
   ) THEN 'no longer present'
 END
) "Status"
FROM usr_t_user_reg
WHERE db_instance = 'DB1'
AND username NOT IN
(
 SELECT username
 FROM usr_t_user_reg
 INTERSECT
 SELECT username
 FROM SYS.dba_users
);

起初我認為它會正常工作，然後我注意到第一個案例有點被忽略了。我有一些尚未註冊的使用者，他們沒有顯示在輸出中。如果我將第一種情況用作獨立查詢

SELECT username 
FROM SYS.dba_users
MINUS
SELECT username 
FROM usr_t_user_reg
WHERE db_instance = 'DB1';

然後它工作。只要我想在整個查詢中使用它（即使沒有CREATE命令）它就不起作用。第二種情況沒有任何問題。

我想我錯過了一些東西，但我不知道是什麼。

其他注意事項

我想我忘了提一件事。數據庫上有多個實例，通過視圖我想知道哪個實例上有一個尚未註冊或不再可用的使用者。我要創建的視圖應該具有與此類似的輸出：

| username | instance |     status     |
|----------|----------|----------------|
|   XYA    |   DB1    | not registered |
|   XYB    |   DB2    | not registered |
|   XYC    |   DB3    | non-existend   |

在我看來，您似乎只詢問註冊使用者，因為SELECT作為視圖的基礎是：

SELECT 
[...]
FROM usr_t_user_reg

因此，“一些尚未註冊且未在輸出中顯示的使用者”是可以預期的。

有這樣的東西會顯示未（尚未）註冊的使用者：

 SELECT
   u.username "User", 
   r.db_instance "Instance",
   NVL(r.db_instance,'not registered yet') "Status"
 FROM 
   SYS.dba_users u LEFT OUTER JOIN usr_t_user_reg r ON u.username = r.username;

但是，這不會給那些仍在“註冊”表中但不再是 DB 使用者的使用者，因為您可以使用並行：

 SELECT
   r.username "User", 
   r.db_instance "Instance",
   CASE NVL(u.username,'X') WHEN 'X' THEN 'not a user anymore' ELSE 'OK' END "Status"
 FROM SYS.dba_users u RIGHT OUTER JOIN usr_t_user_reg r ON u.username = r.username;

上面的 SQL 可能可以很好地結合起來，並且可能不是最佳的，但它確實適用於快速測試……

編輯： 結合上述兩個查詢的完整外連接版本：

 SELECT
   NVL(r.username, u.username) "User", 
   r.db_instance "Instance",
   CASE NVL(u.username,'X') WHEN 'X' THEN 'not a user anymore' ELSE 
     CASE NVL(r.db_instance,'X') WHEN 'X' THEN 'not registered' ELSE 'OK' END
   END "Status"
 FROM SYS.dba_users u FULL OUTER JOIN usr_t_user_reg r ON u.username = r.username;

編輯 2： 一個版本還提供使用者未註冊的實例名稱、不存在的使用者註冊到的實例名稱，並過濾掉所有狀態為 OK 的情況：

SELECT * 
FROM (
 WITH t1 AS (
   SELECT 
     u.username, 
     i.db_instance
   FROM SYS.dba_users u, ( SELECT DISTINCT db_instance FROM usr_t_user_reg ) i
 )
 SELECT 
   t1.username "User", 
   t1.db_instance "Instance", 
   CASE NVL(r.db_instance, 'X') WHEN 'X' THEN 'not registered' ELSE 'OK' END "Status"
 FROM t1 LEFT OUTER JOIN usr_t_user_reg r ON t1.username = r.username AND t1.db_instance = NVL(r.db_instance, t1.db_instance)
 union
 SELECT
   r.username "User", 
   r.db_instance "Instance",
   CASE NVL(u.username,'X') WHEN 'X' THEN 'not a user anymore' ELSE 'OK' END "Status"
 FROM SYS.dba_users u RIGHT OUTER JOIN usr_t_user_reg r ON u.username = r.username
)
WHERE "Status"!='OK';

它開始變得更醜陋，甚至更不理想，但在測試設置上仍然有效。

1. 您的查詢有很多奇怪的事情，您的主要選擇選擇不在 sys.dba 使用者中的使用者名（您使用 youtNOT IN子句排除它們，另一方面，您的第一個案例涉及來自 SYS.dba_users 的使用者。因此該子句永遠不會被選中。這就是您認為它會被忽略的原因。
2. 有時您引用使用者from usr_t_user_reg where db_instance='DB1'有時您引用所有使用者from usr_t_user_reg
3. 基本集合論：A minus (B intersect A) = A minus B. 因此您在 NOT IN 子句中的選擇可以簡化為等效的NOT IN select username from sys.dba_users
4. 要檢索分佈到多個表的數據，您應該盡可能使用連接運算符。這是使用 SQL 時的首選方法。這將使您的查詢更簡單

引用自：https://dba.stackexchange.com/questions/48796