时间戳

首页 > 解决方案 > 如何计算子查询中的条目数

sql - 如何计算子查询中的条目数

问题描述

我想计算每个贷款 ID 的帐号出现的次数，而不是每个 lt.type。

SELECT *
FROM
(
SELECT DISTINCT a.ACCOUNTNUMBER AS [Account Number]
    , CONCAT(n.FIRST, ' ', n.MIDDLE, ' ', n.LAST) AS [Member Name]
    , l.id AS [Loan ID]
    , COUNT(a.ACCOUNTNUMBER)
        OVER(partition by a.ACCOUNTNUMBER) as [Number of Tracking Record] 
    , 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
GROUP BY a.ACCOUNTNUMBER, n.FIRST, n.MIDDLE, n.LAST, l.id, n.email, 
n.HOMEPHONE, lt.type
) MyQuery
WHERE MyQuery.[Number of Tracking Record] >= 2
GROUP BY [Account Number], [Member Name], [Loan ID], [Number of Tracking 
Record], [Email], [Phone Number]
ORDER BY [Account Number]

这是我的查询现在给我的

结果应该是这样的

标签： sqlsql-server

解决方案

您不必要地混合了很多东西，我认为您并不真正了解目的分组。我相信你可能只需要去掉所有这些东西。

SELECT * FROM
(
SELECT a.ACCOUNTNUMBER AS [Account Number]
    , CONCAT(n.FIRST, ' ', n.MIDDLE, ' ', n.LAST) AS [Member Name]
    , l.id AS [Loan ID]
    , COUNT(a.ACCOUNTNUMBER) OVER (partition by l.id) as [Number of Tracking Record] 
    , 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
) MyQuery
WHERE MyQuery.[Number of Tracking Record] >= 2
ORDER BY [Account Number];

推荐阅读