sql-server - 并非所有记录都显示在 SQL 结果中

Question

我有一个 SQL 查询返回给我一个分析师列表，其中包含他们提交的索赔数量和金额。我知道特定客户 (6) 有多少分析师。我遇到的问题是当我运行查询时，只弹出 4 个。如果我注释掉 WHERE 子句（它给了我每个分析师的具体金额），我会得到所有的分析师，但他们的金额都是一样的。如何找回丢失的分析师？我不在乎他们是否返回 0 ......事实上，这就是我试图与计数和总和一起弄清楚的。非常感谢所有帮助。这是我的代码：

SELECT 
    a.auditorID, 
    fName,
    COUNT(DISTINCT case when claims.dateon >='20200726' AND claims.dateon <= '20200909' AND entries.errorCode NOT IN('DP','RB','DN','WP','WA','CE','RC','SI','CI','PE','OV') then claims.rID end) as rTotal1,
    SUM(case when claims.dateon >= '20200726' AND claims.dateon <= '20200909' AND entries.errorCode NOT IN('DP','RB','DN','WP','WA','CE','RC','SI','CI','PE','OV') then entries.refundDue else 0.0 end) as rate1,
    COUNT(DISTINCT case when claims.dateon >='20200726' AND claims.dateon <= '20200909' AND entries.errorCode IN('DP','RB','DN','WP','WA','CE','RC','SI','CI','PE','OV') then claims.rID end) as pTotal1,
    SUM(case when claims.dateon >= '20200726' AND claims.dateon <= '20200909' AND entries.errorCode IN('DP','RB','DN','WP','WA','CE','RC','SI','CI','PE','OV') then entries.refundDue else 0.0 end) as payment1
    
FROM auditors a

INNER JOIN
(
SELECT auditorID, assignments.clientID FROM assignments INNER JOIN assignmentCarriers ac ON ac.acID=Assignments.acID WHERE isAssignment='True' GROUP BY auditorID, assignments.clientID
) tAssignments ON tAssignments.auditorID=a.auditorID

INNER JOIN 
(
SELECT clientID, code FROM clients WHERE code='ABBL'
) tClients ON tClients.clientID=tAssignments.clientID

INNER JOIN claims ON claims.client=tClients.code
INNER JOIN entries ON claims.rID = entries.rid

WHERE claims.auditorID=a.auditorID

GROUP BY a.auditorID, fName


ORDER BY fName

这是我在没有 SUM 或 COUNT 的情况下运行代码时得到的结果......

98  User 1
99  User 2
21  User 3
61  User 4
103 User 5
172 User 6

这里有约束

98  User 1  17    147346.3000   1    9451.1600
21  User 2  0     0.0000        21   182958.5100
61  User 3  5     36970.0000    81   353592.8000
103 User 4  534   319697.5774   58   234350.7900

标签： sql-serversql-server-2008