SQL Server 2008连接两个查询

Question

我在这里忽略了一些微不足道的东西。这个查询没有任何特殊原因，只是尝试练习连接两个查询。我得到的错误信息是:

Msg 156, Level 15, State 1, Line 10
Incorrect syntax near the keyword 'inner'.
Msg 156, Level 15, State 1, Line 16
Incorrect syntax near the keyword 'as'.

查询条件为：

select t.countyName, x.countyName
    from
    (

    SELECT DISTINCT Patient.patientid, tblStateCounties.countyName, Patient.countyCode
    FROM         Patient INNER JOIN
                          tblStateCounties ON Patient.stateCode = tblStateCounties.stateCode AND Patient.countyCode = tblStateCounties.countyCode
    WHERE     (Patient.patientage > 80)
    ) 
    inner join 
    (
    SELECT DISTINCT Patient.patientid, tblStateCounties.countyName, Patient.countyCode
    FROM         Patient INNER JOIN
                          tblStateCounties ON Patient.stateCode = tblStateCounties.stateCode AND Patient.countyCode = tblStateCounties.countyCode
    WHERE     (Patient.patientage < 80)
    ) as x on t.countyname=x.countyname

- wootscootinboogie

---

你在第一个内连接中忘记设置t别名了。 - Gonzalo.-

Answer 1

你在第一个子查询中忘记使用别名(alias)。

- Conan

Answer 2

select t.countyName, x.countyName
from
(

     SELECT DISTINCT Patient.patientid, tblStateCounties.countyName, Patient.countyCode
     FROM         Patient 
     INNER JOIN tblStateCounties ON Patient.stateCode = tblStateCounties.stateCode 
                                 AND Patient.countyCode = tblStateCounties.countyCode
      WHERE     (Patient.patientage > 80)
) rsT
inner join 
(
      SELECT DISTINCT Patient.patientid, tblStateCounties.countyName, Patient.countyCode
      FROM         Patient 
      INNER JOIN tblStateCounties ON Patient.stateCode = tblStateCounties.stateCode   
                                 AND Patient.countyCode = tblStateCounties.countyCode
      WHERE     (Patient.patientage < 80)
) rsX on rsT.countyname=rsX.countyname

- Bert

Answer 3

使用

(
    SELECT DISTINCT Patient.patientid, tblStateCounties.countyName, Patient.countyCode
    FROM Patient INNER JOIN tblStateCounties
    ON Patient.stateCode = tblStateCounties.stateCode 
    AND Patient.countyCode = tblStateCounties.countyCode
    WHERE (Patient.patientage > 80)
) as t

- NG.