SQL Server统计重复记录总数的查询需求及求助

问题背景 #

我使用SQL Server Management Studio，需要编写查询统计某查询结果的重复记录总数。已有一个查询能输出连续2周未提交车辆检查表的司机姓名及车辆登记信息，现在要统计该结果的总条数（示例中预期为7）。

原查询（输出目标记录） #

SELECT DriverName, EngineerVehicleReg_FK
FROM tblPDAForms a WITH (NOLOCK) 
JOIN tblGeneralVehicleInformation b WITH (NOLOCK) ON a.EngineerVehicleReg_FK = b.EngineerVehicleReg  
JOIN tblVehicleStatus c WITH (NOLOCK) ON b.VehicleStatus_IDFK = c.VehicleStatus_ID  
JOIN tblDrivers d WITH (NOLOCK) ON a.AggoraAgent_IDFK = d.AggoraAgent_IDFK  
WHERE PDAFormLocation IS NULL  
AND VehicleStatus_ID IN ('1','5','7')
AND Location_IDFK NOT IN ('7','13') AND EndDate IS NULL  
AND DateSubmitted BETWEEN DATEADD(d,-21,GETDATE()) AND DATEADD(d,-7,GETDATE())
GROUP BY DriverName, EngineerVehicleReg_FK
HAVING 
(COUNT(DriverName) > 1) AND
(COUNT(EngineerVehicleReg_FK) > 1)
ORDER BY DriverName

原查询输出结果 #

尝试的查询（未得到预期结果） #

SELECT COUNT(EngineerVehicleReg_FK) AS Reg  
FROM tblPDAForms pda WITH (NOLOCK)  
JOIN tblGeneralVehicleInformation gv WITH (NOLOCK) ON pda.EngineerVehicleReg_FK = gv.EngineerVehicleReg  
JOIN tblVehicleStatus v WITH (NOLOCK) ON gv.VehicleStatus_IDFK = v.VehicleStatus_ID  
JOIN tblDrivers d WITH (NOLOCK) ON pda.AggoraAgent_IDFK = d.AggoraAgent_IDFK  
WHERE PDAFormLocation IS NULL  
AND VehicleStatus_ID IN ('1','5','7') AND Location_IDFK NOT IN ('7','13') AND EndDate IS NULL  
AND DateSubmitted BETWEEN DATEADD(d,-21,GETDATE()) AND DATEADD(d,-7,GETDATE())
GROUP BY EngineerVehicleReg_FK
HAVING COUNT(EngineerVehicleReg_FK) > 1

错误输出（显示每条记录的重复次数） #

期望输出 #

解决方法 #

将第一个查询作为子查询嵌套在外层的COUNT统计中，即可得到目标记录的总条数：

SELECT COUNT(*) AS Reg
FROM (
    SELECT DriverName, EngineerVehicleReg_FK
    FROM tblPDAForms a WITH (NOLOCK) 
    JOIN tblGeneralVehicleInformation b WITH (NOLOCK) ON a.EngineerVehicleReg_FK = b.EngineerVehicleReg  
    JOIN tblVehicleStatus c WITH (NOLOCK) ON b.VehicleStatus_IDFK = c.VehicleStatus_ID  
    JOIN tblDrivers d WITH (NOLOCK) ON a.AggoraAgent_IDFK = d.AggoraAgent_IDFK  
    WHERE PDAFormLocation IS NULL  
    AND VehicleStatus_ID IN ('1','5','7')
    AND Location_IDFK NOT IN ('7','13') AND EndDate IS NULL  
    AND DateSubmitted BETWEEN DATEADD(d,-21,GETDATE()) AND DATEADD(d,-7,GETDATE())
    GROUP BY DriverName, EngineerVehicleReg_FK
    HAVING 
    (COUNT(DriverName) > 1) AND
    (COUNT(EngineerVehicleReg_FK) > 1)
) AS SubQuery

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