mysql - SQL 查询错误 : 'doesn' t know columnName' in Where clause

Question

任何人都可以帮助我，我有点卡住了。

我有一个非常有效的查询

SELECT * , 
       matches - falsepositives AS hits
FROM (
    SELECT c. * , 
           IFNULL( p.total, 0 ) AS matches, 
            (
                SELECT COUNT( * ) 
                FROM ci_falsepositives n
                WHERE n.addressbook_id = c.reference
                AND n.sanction_key
                IN (
                    SELECT sanction_key
                    FROM ci_matched_sanctions
                )
            ) AS falsepositives
    FROM ci_address_book c
    LEFT JOIN (
        SELECT addressbook_id, 
               COUNT( match_id ) AS total
        FROM ci_matched_sanctions
        GROUP BY addressbook_id
    ) AS p 
        ON c.id = p.addressbook_id
) AS S
WHERE matches > 0
ORDER BY hits DESC

但是我想将它更改为对 HITS 大于 0 的地方进行排序，但是它告诉我它不知道 hits... 是因为它是对 2 项的计算吗？

Answer 1

您不能在 WHERE 子句中使用别名列。你需要重写它:

SELECT * , 
       matches - falsepositives AS hits
FROM (
    SELECT c. * , 
           IFNULL( p.total, 0 ) AS matches, 
            (
                SELECT COUNT( * ) 
                FROM ci_falsepositives n
                WHERE n.addressbook_id = c.reference
                AND n.sanction_key
                IN (
                    SELECT sanction_key
                    FROM ci_matched_sanctions
                )
            ) AS falsepositives
    FROM ci_address_book c
    LEFT JOIN (
        SELECT addressbook_id, 
               COUNT( match_id ) AS total
        FROM ci_matched_sanctions
        GROUP BY addressbook_id
    ) AS p 
        ON c.id = p.addressbook_id
) AS S
WHERE matches - falsepositives > 0
ORDER BY hits DESC

或者使用子查询:

SELECT * FROM (
    SELECT * , 
           matches - falsepositives AS hits
    FROM (
        SELECT c. * , 
               IFNULL( p.total, 0 ) AS matches, 
                (
                    SELECT COUNT( * ) 
                    FROM ci_falsepositives n
                    WHERE n.addressbook_id = c.reference
                    AND n.sanction_key
                    IN (
                        SELECT sanction_key
                        FROM ci_matched_sanctions
                    )
                ) AS falsepositives
        FROM ci_address_book c
        LEFT JOIN (
            SELECT addressbook_id, 
                   COUNT( match_id ) AS total
            FROM ci_matched_sanctions
            GROUP BY addressbook_id
        ) AS p 
            ON c.id = p.addressbook_id
    ) AS S
) AS S2
WHERE hits > 0
ORDER BY hits DESC