连接两个子查询或带有子查询的查询SQL Server 2008

连接两个子查询或带有子查询的查询SQL Server 2008

没有样本数据和理想的结果很难说，但是也许这是您要追求的？

;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;