我在SQL Server Management Studio中工作，正在寻找能够计算查询中重复行总数的查询.
目前，我有一个查询，输出司机的姓名和车辆登记，任何司机没有提交车辆判断表连续2周.但我现在正在try 创建第二个查询，该查询给出重复行的总计数.
我现在的查询输出司机姓名和车辆登记，对于连续两周未提交的判断表:
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
以下是输出:
|DriverName|EngineerVehicleReg_FK|
|----------|---------------------|
|Ian |PK19 OVB |
|Jack |BX69 TXG |
|John |FP22 HTE |
|Leigh |FG72 ZTN |
|Noel |FP22 HWW |
|Ross |FL23 RYK |
|Tom |FP22 HWV |
我现在需要第二个查询来提供这些记录的计数.因此，在本例中，我希望能够运行一个查询，它只显示计数为7.
我试过几种方法，最接近的方法是:
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
它输出每条记录显示的次数的计数:
|Reg|
|---|
|2 |
|2 |
|2 |
|2 |
|2 |
|2 |
|2 |
这不是我想要的输出，因为我试图获得以下输出:
|Reg|
|---|
|7 |
任何帮助和帮助都将不胜感激.
-决心 这个问题已经通过将我的第一个查询转换为子查询的建议得到了解决.