Oracle

SQL Count 函式返回不准確的結果

  • July 11, 2018

我正在嘗試在 oracle 中編寫一個查詢,以列出由表空間中的多個列過濾的單個表中包含的記錄數。這是我想出的查詢語法:

SELECT COUNT(CASE WHEN unique_id like '%OU=Users%' and employee_type > '-1' THEN 1 ELSE NULL
        END) AS USERS1
  ,COUNT(CASE WHEN unique_id like '%OU=Vendors%' and employee_id is null and employee_type is null THEN 1 ELSE NULL
         END) AS USERS2
  ,COUNT(CASE WHEN unique_id like '%OU=Temp Users%' and employee_type ='-1' THEN 1 ELSE NULL
         END) AS USERS3
  ,COUNT(CASE WHEN unique_id like '%OU=Service%' THEN 1 ELSE NULL
         END) AS USERS4
  ,COUNT(*) AS USERS5
FROM table_USERS 
WHERE is_terminated = 'False'

這是結果輸出:

users1=1192
users2=38
users3=25
users4=240
users5=1548

結果不正確,它顯示“users5”作為數據庫中存在的使用者總數(1548),當它應該只顯示 53 個不匹配任何使用者類別(users1 - users4)的使用者時。

我錯過了什麼,有沒有更簡單的方法來編寫這個查詢?

看起來您只有“COUNT(*) AS USERS5”,我沒有看到您在哪裡過濾掉其他類型的使用者類別。

這是 Pavel 答案的 CTE(通用表表達式)版本。它避免了許多介面的程序限制,可以用作單個查詢或視圖。

WITH cte AS
(
   SELECT
     COUNT(CASE WHEN unique_id like '%OU=Users%' and employee_type > '-1' THEN 1 ELSE NULL END) AS users1
   , COUNT(CASE WHEN unique_id like '%OU=Vendors%' and employee_id is null and employee_type is null THEN 1 ELSE NULL END) as users2
   , COUNT(CASE WHEN unique_id like '%OU=Temp Users%' and employee_type ='-1' THEN 1 ELSE NULL END) as users3
   , COUNT(CASE WHEN unique_id like '%OU=Service%' THEN 1 ELSE NULL END) as users4
   , COUNT(*) as users_All
   FROM table_USERS 
   WHERE is_terminated = 'False'
)
SELECT users1, users2, users3, users4, users_ALL- (users1+ users2+ users3+ users4)
FROM cte
;

引用自:https://dba.stackexchange.com/questions/211680