如何在WHERE子句中使用派生列LineNo？SQL查询报错求助

这是SQL里一个很典型的执行顺序问题——你没法直接在WHERE子句里引用SELECT列表中刚派生的LineNo字段。原因很简单：SQL的执行顺序是先处理JOIN和WHERE子句，再去计算SELECT里的字段，这时候LineNo这个派生字段还没被生成，数据库自然找不到它。

给你几个可行的解决方案，按需选择：

方案1：把CASE表达式直接移到WHERE子句里 #

既然LineNo是通过CASE生成的，那我们可以直接在WHERE里复用这个CASE逻辑，这样就能直接筛选了：

SELECT 
    tblA.PROJECT_ID, 
    tblB.Line1Hrs, 
    tblB.Line2Hrs, 
    tblB.Line3Hrs, 
    tblB.Line4Hrs, 
    tblB.Line5Hrs, 
    tblB.Line6Hrs, 
    tblB.Line7Hrs,
    CASE 
        WHEN tblB.Line1Hrs > 0 THEN 'Line1'
        WHEN tblB.Line2Hrs > 0 THEN 'Line2'
        WHEN tblB.Line3Hrs > 0 THEN 'Line3'
        WHEN tblB.Line4Hrs > 0 THEN 'Line4'
        WHEN tblB.Line5Hrs > 0 THEN 'Line5'
        WHEN tblB.Line6Hrs > 0 THEN 'Line6'
        WHEN tblB.Line7Hrs > 0 THEN 'Line7'
    END AS LineNo
FROM tblA 
INNER JOIN tblB ON tblA.blah = tblB.blah AND tblA.blab = tblB.blab
WHERE 
    CASE 
        WHEN tblB.Line1Hrs > 0 THEN 'Line1'
        WHEN tblB.Line2Hrs > 0 THEN 'Line2'
        WHEN tblB.Line3Hrs > 0 THEN 'Line3'
        WHEN tblB.Line4Hrs > 0 THEN 'Line4'
        WHEN tblB.Line5Hrs > 0 THEN 'Line5'
        WHEN tblB.Line6Hrs > 0 THEN 'Line6'
        WHEN tblB.Line7Hrs > 0 THEN 'Line7'
    END = 'Line5'

不过这个方案的缺点是重复写了一遍CASE，后续维护起来有点麻烦。

方案2：用CTE（公共表表达式）先计算派生列，再筛选 #

CTE可以先把包含LineNo的数据集生成出来，然后在外层查询里直接用LineNo筛选，代码结构更清晰：

WITH JobLineDetails AS (
    SELECT 
        tblA.PROJECT_ID, 
        tblB.Line1Hrs, 
        tblB.Line2Hrs, 
        tblB.Line3Hrs, 
        tblB.Line4Hrs, 
        tblB.Line5Hrs, 
        tblB.Line6Hrs, 
        tblB.Line7Hrs,
        CASE 
            WHEN tblB.Line1Hrs > 0 THEN 'Line1'
            WHEN tblB.Line2Hrs > 0 THEN 'Line2'
            WHEN tblB.Line3Hrs > 0 THEN 'Line3'
            WHEN tblB.Line4Hrs > 0 THEN 'Line4'
            WHEN tblB.Line5Hrs > 0 THEN 'Line5'
            WHEN tblB.Line6Hrs > 0 THEN 'Line6'
            WHEN tblB.Line7Hrs > 0 THEN 'Line7'
        END AS LineNo
    FROM tblA 
    INNER JOIN tblB ON tblA.blah = tblB.blah AND tblA.blab = tblB.blab
)
SELECT *
FROM JobLineDetails
WHERE LineNo = 'Line5'

方案3：用子查询嵌套 #

和CTE的逻辑类似，只是把派生列的计算放到子查询里，外层再进行筛选：

SELECT *
FROM (
    SELECT 
        tblA.PROJECT_ID, 
        tblB.Line1Hrs, 
        tblB.Line2Hrs, 
        tblB.Line3Hrs, 
        tblB.Line4Hrs, 
        tblB.Line5Hrs, 
        tblB.Line6Hrs, 
        tblB.Line7Hrs,
        CASE 
            WHEN tblB.Line1Hrs > 0 THEN 'Line1'
            WHEN tblB.Line2Hrs > 0 THEN 'Line2'
            WHEN tblB.Line3Hrs > 0 THEN 'Line3'
            WHEN tblB.Line4Hrs > 0 THEN 'Line4'
            WHEN tblB.Line5Hrs > 0 THEN 'Line5'
            WHEN tblB.Line6Hrs > 0 THEN 'Line6'
            WHEN tblB.Line7Hrs > 0 THEN 'Line7'
        END AS LineNo
    FROM tblA 
    INNER JOIN tblB ON tblA.blah = tblB.blah AND tblA.blab = tblB.blab
) AS SubQuery
WHERE LineNo = 'Line5'

额外优化：如果业务逻辑允许，直接简化WHERE条件 #

如果你的需求只是筛选出Line5Hrs>0的记录（不管其他LineHrs是否有值），那完全可以跳过CASE，直接写：

SELECT 
    tblA.PROJECT_ID, 
    tblB.Line1Hrs, 
    tblB.Line2Hrs, 
    tblB.Line3Hrs, 
    tblB.Line4Hrs, 
    tblB.Line5Hrs, 
    tblB.Line6Hrs, 
    tblB.Line7Hrs,
    'Line5' AS LineNo
FROM tblA 
INNER JOIN tblB ON tblA.blah = tblB.blah AND tblA.blab = tblB.blab
WHERE tblB.Line5Hrs > 0

这样效率会更高，因为不用执行CASE的分支判断。

内容的提问来源于stack exchange，提问作者potatochucker