SQL 问题 - 我的 WHERE 子句有什么问题-解网

问：

自从我不得不使用 SQL 和编写查询以来已经有几年了，但我得到了我意想不到的结果。我花了很多时间查看它，我确信我遗漏了一些明显的东西，但是，当我在下面运行它时，我的查询返回的值不是我的语句中的值。RateCodeWHERE

我做错了什么？我觉得这是一个足够基本的查询（我开始恐慌，因为我失去了技能）

SELECT
    cs.Acct AS Site,
    cs.Addr1 AS Address,
    cs.City,
    cs.State,
    m.TypeDescr AS Service,
    se.Quantity,
    se.SvcCode,
    se.SvcDescr,
    sf.FreqDescr AS Frequency,
    se.SvcId, 
    se.StartDate,
    se.EndDate,
    sr.ReasonDescr AS Reason,
    svr.RateCode,  
    svr.StartDate AS RateStartDate,
    svr.EndDate AS RateEndDate

FROM
    wiSvcEquipment AS se
    LEFT JOIN wiCustSite    AS cs  ON cs.SiteId    = se.SiteId
    LEFT JOIN wiMaterial    AS m   ON m.MatSubType = se.MatSubType
    LEFT JOIN wiSvcReason   AS sr  ON sr.ReasonID  = se.ReasonID
    LEFT JOIN wiSvcVendRate AS svr ON svr.SvcId    = se.SvcID
    LEFT JOIN wiSvcCustRate as scr ON scr.RateCode = se.RateCode
    LEFT JOIN wiSvcFreq     AS sf  ON sf.FreqID    = se.FreqID

WHERE
    se.CustId = ‘013714’
    OR
    se.CustId = ‘014831’
    AND 
    svr.RateCode IN (
        ‘CHGCMBHAUL’, ‘CHGHAUFLAT’, ‘CHGHAUL2’, ‘CHGHAUL3’,
        ‘CHGHAULCOM’, ‘CHGHAULFE’, ‘CHGHAULING’, ‘CHGHAULMIN’,
        ‘CHGVCMBHAU’, ‘CHGVHAUFLT’, ‘CHGVHAULFE’, ‘CHGVHAULNG’,
        ‘CHGWKDHAUL’, ‘CLIHAUFLAT’, ‘CLIHAULFE’, ‘CLIHAULING’
    ) ;

SQL 布尔代数

WHERE
    se.CustId IN ( '013714', '014831' )
    AND 
    svr.RateCode IN (
        'CHGCMBHAUL', 'CHGHAUFLAT', 'CHGHAUL2', 'CHGHAUL3',
        'CHGHAULCOM', 'CHGHAULFE', 'CHGHAULING', 'CHGHAULMIN',
        'CHGVCMBHAU', 'CHGVHAUFLT', 'CHGVHAULFE', 'CHGVHAULNG',
        'CHGWKDHAUL', 'CLIHAUFLAT', 'CLIHAULFE', 'CLIHAULING'
    );

或者使用显式逻辑组：

WHERE
    (
        se.CustId = '013714'
        OR
        se.CustId = '014831'
    )
    AND 
    svr.RateCode IN (
        'CHGCMBHAUL', 'CHGHAUFLAT', 'CHGHAUL2', 'CHGHAUL3',
        'CHGHAULCOM', 'CHGHAULFE', 'CHGHAULING', 'CHGHAULMIN',
        'CHGVCMBHAU', 'CHGVHAUFLT', 'CHGVHAULFE', 'CHGVHAULNG',
        'CHGWKDHAUL', 'CLIHAUFLAT', 'CLIHAULFE', 'CLIHAULING'
    );

上面的两个子句是完全相同的：在SQL中，运算符只是一系列比较的句法糖。x IN ( a, b, c )( x = a OR x = b OR x = c )

1赞 Z . 2/6/2021 #2

你是说

(se.CustId = ‘013714’ OR se.CustId = ‘014831’)

AND 优先于 OR

1赞 Gordon Linoff 2/6/2021 #3

我可以看到三件事：

对于第一个条件，你需要括号，或者更好的是。IN
一系列 S 中除第一个表外的所有条件都应在子句中。LEFT JOINON
您的字符串有智能引号，但它们应该是“直”单引号。

所以：

SELECT . . .
FROM wiSvcEquipment se
     wiCustSite cs 
     ON cs.SiteId = se.SiteId LEFT JOIN
     wiMaterial m  
     ON m.MatSubType = se.MatSubType LEFT JOIN
     wiSvcReason sr 
     ON sr.ReasonID = se.ReasonID AND
            svr.RateCode IN ('CHGCMBHAUL', 'CHGHAUFLAT', 'CHGHAUL2', 'CHGHAUL3',
                             'CHGHAULCOM', 'CHGHAULFE', 'CHGHAULING', 'CHGHAULMIN’,
                             'CHGVCMBHAU', 'CHGVHAUFLT', 'CHGVHAULFE', 'CHGVHAULNG',
                             'CHGWKDHAUL', 'CLIHAUFLA’, 'CLIHAULFE', 'CLIHAULING'
                            ) LEFT JOIN

        LEFT JOIN
     wiSvcVendRate svr
     ON svr.SvcId = se.SvcID LEFT JOIN
     wiSvcCustRate scr
     ON scr.RateCode = se.RateCode LEFT JOIN
     wiSvcFreq sf 
     ON sf.FreqID = se.FreqID
WHERE se.CustId IN ('013714', '014831')

WHERE
    (se.CustId = ‘013714’
    OR
    se.CustId = ‘014831’)
    AND 
    svr.RateCode IN (
        ‘CHGCMBHAUL’, ‘CHGHAUFLAT’, ‘CHGHAUL2’, ‘CHGHAUL3’,
        ‘CHGHAULCOM’, ‘CHGHAULFE’, ‘CHGHAULING’, ‘CHGHAULMIN’,
        ‘CHGVCMBHAU’, ‘CHGVHAUFLT’, ‘CHGVHAULFE’, ‘CHGVHAULNG’,
        ‘CHGWKDHAUL’, ‘CLIHAUFLAT’, ‘CLIHAULFE’, ‘CLIHAULING’
    ) ;

上一个：在 case 语句中使用 Max（boolean）

下一个：在另一个表中添加行存在标志，并使用 group by

SQL 问题 - 我的 WHERE 子句有什么问题

SQL Question - What is wrong with my WHERE clause

评论

评论