没有样本数据和理想的结果很难说，但是也许这是您要追求的？
;WITH x AS ( SELECt c.CountyName, v.patientCounty, v.patientState, p.patientID FROM dbo.tblPatient AS p INNER JOIN dbo.tblPatientVisits AS v ON p.patientID = v.patientID INNER JOIN dbo.tblStateCounties AS c ON v.patientState = c.stateCode AND v.patientCounty = c.countyCode WHERe c.stateCode = '21'),y AS (SELECt CountyName, c = COUNT(*) FROM x GROUP BY CountyName),z AS (SELECt CountyName, c = COUNT(PatientID) FROM x GROUP BY CountyName, patientState, PatientID HAVINg COUNT(*)>1)SELECt y.countyName, reAdmits = MAX(COALESCE(z.c, 0)), totalVisits = MAX(y.c)FROM y LEFT OUTER JOIN zON y.CountyName = z.CountyNameGROUP BY y.CountyName;
