sql-server - 如何在条件中正确使用 AND/OR

Question

我有一个这样的查询:

SELECT AVG([DC].[ContractedAmount]) AS [AverageContractedAmount]      ,MAX([DC].[ContractedAmount]) AS [MaxContractedAmount]      ,MIN([DC].[ContractedAmount]) AS [MinContractedAmount]FROM [DesignCustomer] AS [DC]INNER JOIN [Design] AS [D] ON [DC].[DesignKey] = [D].[DesignKey]INNER JOIN [Task] AS [T] ON [D].[DesignKey] = [t].[DesignKey]INNER JOIN [ProjectDesign] AS [PD] ON [D].[DesignKey] = [PD].[DesignKey]INNER JOIN [Project] AS [P] ON [PD].[ProjectKey] = [P].[ProjectKey]INNER JOIN [Address] AS [A] ON [A].[AddressGuid] = [P].[ProjectGuid]WHERE [DC].[ContractedAmount] != 0.00AND [DC].[CustomerKey] = @CustomerKeyOR [A].[RegionKey] = @RegionKeyOR [A].[StateKey] = @StateKey

出于某种原因，当我执行 [MinContractedAmount] 返回 0.00 值时，它不关心我的 where 子句 [DC].[ContractedAmount] != 0.00，为什么这不起作用？

注意[DC].[ContractedAmount]是一个money字段

Answer 1

您需要为您的 WHERE 子句使用 () 因为 AND 运算符具有优先权，现在解释器理解这一点

WHERE ([DC].[ContractedAmount] != 0.00 AND [DC].[CustomerKey] = @CustomerKey)
 OR [A].[RegionKey] = @RegionKey
 OR [A].[StateKey] = @StateKey

你可能想要

WHERE [DC].[ContractedAmount] != 0.00 
  AND ( [DC].[CustomerKey] = @CustomerKey
     OR [A].[RegionKey] = @RegionKey
     OR [A].[StateKey] = @StateKey )