嘗試執行子查詢時出錯

我在讓子查詢正常執行時遇到問題。在我添加子查詢之前，一切都執行得又快又準。我收到錯誤消息：“消息 4145，級別 15，狀態 1，第 66 行，在預期條件的上下文中指定的非布爾類型表達式，靠近‘，’。”

FROM FactPerson FP
JOIN FactCrash FC ON FP.CrashSK = FC.CrashPK
JOIN DimHWYType DHT ON DHT.HWYTypeCode = FC.HighwayTypeCode
JOIN FactVehicle FV ON FV.CrashSK = FC.CrashPK
JOIN DimVehicleType DVT ON DVT.VehicleTypeCode = FV.VehicleTypeCode
WHERE (FC.HighwayTypeCode = 'A' OR FC.HighwayTypeCode = 'B' OR FC.HighwayTypeCode = 'C')
AND (FV.VehicleTypeCode = 'A' OR FV.VehicleTypeCode = 'B' OR FV.VehicleTypeCode = 'S')
AND (FP.Age, FP.PersonType) IN
   (SELECT FP.Age, FP.PersonType
   FROM FactPerson FP
   WHERE (FP.PersonType = 'A')
   AND (FP.Age BETWEEN 18 AND 21))

我也嘗試過 EXISTS，但它返回的記錄超出了年齡標準。

FROM FactPerson FP
JOIN FactCrash FC ON FP.CrashSK = FC.CrashPK
JOIN DimHWYType DHT ON DHT.HWYTypeCode = FC.HighwayTypeCode
JOIN FactVehicle FV ON FV.CrashSK = FC.CrashPK
JOIN DimVehicleType DVT ON DVT.VehicleTypeCode = FV.VehicleTypeCode
WHERE (FC.HighwayTypeCode = 'A' OR FC.HighwayTypeCode = 'B' OR FC.HighwayTypeCode = 'C')
AND (FV.VehicleTypeCode = 'A' OR FV.VehicleTypeCode = 'B' OR FV.VehicleTypeCode = 'S')
AND EXISTS
   (SELECT FP.Age, FP.PersonType
   FROM FactPerson FP
   WHERE (FP.PersonType = 'A')
   AND (FP.Age BETWEEN 18 AND 21))

這AND (a,b) IN (SELECT x,y FROM ... WHERE)是標準 SQL，但尚未在 SQL Server 中實現。您可以將其替換為相關的EXISTS子查詢：

AND EXISTS (SELECT * FROM ... WHERE ... AND x=a AND y=b)

您的EXISTS子查詢不正確，因為它不相關。

在您的特定查詢中，應寫為：

AND EXISTS 
   (SELECT *
   FROM FactPerson FPi
   WHERE FPi.PersonType = 'A'
   AND FPi.Age BETWEEN 18 AND 21
   AND FPi.Age = FP.Age AND FPi.PersonType = FP.PersonType)

但是所有的子查詢確實是不必要的。簡單的條件是等價的：

AND FP.PersonType = 'A' 
AND FP.Age BETWEEN 18 AND 21

引用自：https://dba.stackexchange.com/questions/137274