Oracle
SQL Count 函式返回不准確的結果
我正在嘗試在 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 ;