我对查询结果感到惊讶,希望有人能解释一下。
查询有点大,所以我将其提炼为关键部分:
SELECT *
FROM A
INNER JOIN B ON A.id = B.id
LEFT JOIN C ON C.ID = B.ID
WHERE A.Dt = '2016-06-23'
AND (B.StatusCode=' ' OR C.Code <> 9)
我收到的日期不是2016年6月23日,尽管我的where条款中有。我认为这与在一个表达式中组合左联接表和内部联接表中where子句中的列有关,但以前从未经历过这种情况。
UPDATE:添加实际查询
SELECT *
FROM CERT2.cube_mbbal_Daily_Balances BAL
INNER JOIN CERT.Dim_Account A ON A.AccountKey = BAL.AccountKey
LEFT JOIN CERT2.Dim_LoanAccount LA ON A.AccountKey = LA.AccountKey
WHERE
(A.AccountClassification IN ('Checking', 'Savings')
AND AccountStatus IN ('1', '3', '5', '6', '7'))
OR
(A.AccountClassification IN ('Time')
AND AccountStatus IN ('1', '5', '7'))
OR
(A.AccountClassification IN ('Loan')
AND (AccountStatus <> 'C' OR RiskCode <> 9))
AND BAL.DateOfFinancialMeasure = 20160623
AND
在OR
之前求值。这就像你在评估2 + 5 + 7 * 5
,想知道为什么你得到的是42
而不是70
。
尝试:
select *
from CERT2.cube_mbbal_Daily_Balances BAL
inner join CERT.Dim_Account A
on A.AccountKey = BAL.AccountKey
left join CERT2.Dim_LoanAccount LA
on A.AccountKey = LA.AccountKey
where (
(A.AccountClassification in ('Checking', 'Savings') and AccountStatus in ('1', '3', '5', '6', '7'))
or (A.AccountClassification in ('Time') and AccountStatus in ('1', '5', '7'))
or (A.AccountClassification in ('Loan') and (AccountStatus <> 'C' or RiskCode <> 9))
)
and BAL.DateOfFinancialMeasure = 20160623