如何按Loan ID统计lt.type=46的账户跟踪记录数而非账户总记录数

我来帮你调整这个SQL查询，满足按每个Loan ID统计对应账户下lt.type=46记录数的需求。你的核心问题是原查询用partition by a.ACCOUNTNUMBER导致统计的是整个账户的记录总数，而非单个Loan ID对应的数量，以下是调整后的方案：

调整后的SQL查询 #

SELECT * FROM (
 SELECT DISTINCT a.ACCOUNTNUMBER AS [Account Number]
 , CONCAT(n.FIRST, ' ', n.MIDDLE, ' ', n.LAST) AS [Member Name]
 , l.id AS [Loan ID]
 -- 关键修改：切换统计维度为Loan ID，用lt.ID确保计数准确
 , COUNT(lt.ID) OVER(partition by l.id) as [Number of Tracking Records]
 , n.EMAIL AS [Email]
 , n.HOMEPHONE AS [Phone Number]
 FROM dbo.account a
 INNER JOIN dbo.LOAN l ON a.ACCOUNTNUMBER = l.PARENTACCOUNT
 INNER JOIN dbo.LOANTRACKING lt ON l.PARENTACCOUNT = lt.PARENTACCOUNT AND l.ID = lt.ID
 INNER JOIN dbo.NAME n ON a.ACCOUNTNUMBER = n.PARENTACCOUNT
 WHERE lt.type = 46
 AND l.ProcessDate = CONVERT(VARCHAR(8), dateadd(day,-1, getdate()), 112)
 AND lt.ProcessDate = CONVERT(VARCHAR(8), dateadd(day,-1, getdate()), 112)
 AND n.ProcessDate = CONVERT(VARCHAR(8), dateadd(day,-1, getdate()), 112)
 AND a.ProcessDate = CONVERT(VARCHAR(8), dateadd(day,-1, getdate()), 112)
 AND l.CLOSEDATE IS NULL
 AND lt.EXPIREDATE IS NULL
 AND n.type = 0
 AND a.ACCOUNTNUMBER NOT IN (SELECT a.ACCOUNTNUMBER FROM dbo.ACCOUNT a INNER JOIN dbo.LOANTRACKING lt ON a.ACCOUNTNUMBER = lt.PARENTACCOUNT WHERE lt.type = 36)
 ) MyQuery
 WHERE MyQuery.[Number of Tracking Records] >= 3
 ORDER BY [Account Number], MyQuery.[Loan ID]

关键修改说明 #

• 统计维度切换：把COUNT(a.ACCOUNTNUMBER) OVER(partition by a.ACCOUNTNUMBER)改成COUNT(lt.ID) OVER(partition by l.id)，这样就会针对每个Loan ID（l.id）统计关联的lt.type=46记录数，而非整个账户的总数。
• 计数准确性优化：使用lt.ID作为计数对象，避免因关联表产生重复行导致的错误计数（如果ACCOUNTNUMBER在关联逻辑中存在重复的话）。

示例数据对比 #

当前结果（按账户统计） #

期望结果（按Loan ID统计） #

内容的提问来源于stack exchange，提问作者lexirainbow