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

爱蛋

怪物编辑：查询将现在运行，但返回错误答案。添加了一个粗略的结构。PatientID不是tblPatientVisits表中的主键，因为同一个病人ID可多次出现。
+ d4 p1 x+ V% E列出每个县名，但每个计数（s.countyName）和计数（t.countyname）为1
PatientVisits   PatientID   intPatientState    varchar(2)patientCounty   varchar(3)visitNumber int - PKtblStateCounties    CPK stateCode   varchar(2)CPK countyCode  varchar(3)countyName  varchar(25)SELECT t.countyName,   count(t.countyName) as reAdmits ,count(s.countyName) as totalVisitsFROM  SELECT countyName,count(countyName) AS readmitCounts    FROM      SELECT tblPatient.patientID  tblStateCounties.countyName        FROM tblPatient        INNER JOIN tblPatientVisits            ON tblPatient.patientID = tblPatientVisits.patientID        INNER JOIN tblStateCounties            ON tblPatientVisits.patientState = tblStateCounties.stateCode                AND tblPatientVisits.patientCounty = tblStateCounties.countyCode        GROUP BY tblPatient.patientID  tblStateCounties.stateCode  tblStateCounties.countyName        HAVING (COUNT(tblPatient.patientID) > 1)         AND (tblStateCounties.stateCode =          t    GROUP BY countyname   ) tINNER JOIN  SELECT countyName    FROM      SELECT tblStateCounties.countyName  COUNT(tblStateCounties.countyName) AS counts        FROM tblPatient        INNER JOIN tblPatientVisits            ON tblPatient.patientID = tblPatientVisits.patientID        INNER JOIN tblStateCounties            ON tblPatientVisits.patientState = tblStateCounties.stateCode                AND tblPatientVisits.patientCounty = tblStateCounties.countyCode        WHERE (tblStateCounties.stateCode =     GROUP BY tblStateCounties.countyName       ) z   ) s    ON s.countyName = t.countyName    group by s.countyname,t.countyname编辑：我有一个查询，现在运行，但它已经回来了
                                                               
    解决方案:                                                               
                                                                很难说没有样本数据和理想的结果，但也许这是你想要追求的？
;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;