时间戳

首页 > 解决方案 > 在 where 子句中有多个条件的 SQL 查询

sql - 在 where 子句中有多个条件的 SQL 查询

问题描述

我正在尝试执行以下查询，但它不满足我在 where 子句中提到的条件。它必须首先选择 36、48、60 中的术语，如果术语为 48，则值不应介于 -107 到 -305 之间，如果术语为 60，则值不应介于 0 到 87 之间且不包含-1304,-1204，如果 term 为 36，则值不应超过 -300。但是当我运行查询时，它给出了我在排除项中提到的所有值。请帮忙

select distinct
Market,
(select top 1 LDCAccountidentifier from siteidentification where siteoid=#final.siteoid) as CustAccNum,
SiteOID,
RtlrContractIdentifier,
ContractOID,
ContractType,
ContractStatus,
Term,
ProductCode,
SigningDate,
FlowStartDate,
FlowEndDate,
RenewalDate,
Dateadd(dd,(term*365/12),flowstartdate) as [FSD+Term],
datediff(dd,Dateadd(dd,(term*365/12),flowstartdate),RenewalDate) as DifferenceinDays,
UsageFrom,
UsageTo,
DealFSD,
DealFED,
ContractUpdateDate,
UsageUpdateDate,
DealUpdateDate,
replace(replace(ErrorMessage, char(13),', '), Char(10),'') as ErrorMessage
from #final
where term in(36,48,60)
or (term=48 and datediff(dd,Dateadd(dd,1460,flowstartdate),RenewalDate) not between -107 and -305)
or (term=60 and datediff(dd,Dateadd(dd,1825,flowstartdate),RenewalDate)not between 0 and 87)
or (term=60 and datediff(dd,Dateadd(dd,1825,flowstartdate),RenewalDate) not in(-1304,-1204))
or (term=36 and datediff(dd,Dateadd(dd,1095,flowstartdate),RenewalDate)<-300)

标签： sqlsql-servermultiple-columnswhere-clause

解决方案

您可以在下面尝试使用大括号设置 OR 运算符的优先级

select distinct
Market,
(select top 1 LDCAccountidentifier from siteidentification where siteoid=#final.siteoid) as CustAccNum,
SiteOID,
RtlrContractIdentifier,
ContractOID,
ContractType,
ContractStatus,
Term,
ProductCode,
SigningDate,
FlowStartDate,
FlowEndDate,
RenewalDate,
Dateadd(dd,(term*365/12),flowstartdate) as [FSD+Term],
datediff(dd,Dateadd(dd,(term*365/12),flowstartdate),RenewalDate) as DifferenceinDays,
UsageFrom,
UsageTo,
DealFSD,
DealFED,
ContractUpdateDate,
UsageUpdateDate,
DealUpdateDate,
replace(replace(ErrorMessage, char(13),', '), Char(10),'') as ErrorMessage
from #final
where term in(36,48,60) and (
(term=48 and datediff(dd,Dateadd(dd,1460,flowstartdate),RenewalDate) not between -305 and -107)
or (term=60 and datediff(dd,Dateadd(dd,1825,flowstartdate),RenewalDate)not between 0 and 87)
or (term=60 and datediff(dd,Dateadd(dd,1825,flowstartdate),RenewalDate) not in(-1304,-1204))
or (term=36 and datediff(dd,Dateadd(dd,1095,flowstartdate),RenewalDate)<-300))

推荐阅读