MySql 重用 WHERE 子句的别名

Question

我有一个查询，用于检查从搜索文本框中输入的每个关键字，并且必须返回最匹配的关键字。

问题是，我想排除返回行中所有值为 0 的 KW_MATCHED。

SELECT
    A1.*, (
        SELECT
            sum(
                CASE
                WHEN (A1.ID = A2.tag_id)
                AND (
                    A2.keyword = 'keyword1' || A2.keyword = 'keyword2'
                ) THEN
                    1
                ELSE
                    0
                END
            )
        FROM
            tbl_article_tags A2
    ) AS KW_MATCHED
FROM
    tbl_article A1
ORDER BY
    KW_MATCHED DESC

结果:

+----+---------------+----------------+------------+
| ID | Title         | Content        | KW_MATCHED |
+----+---------------+----------------+------------+
| 1  | title         | Lorem Ipsum... | 7          |
+----+---------------+----------------+------------+
| 2  | another title | Lorem Ipsum... | 5          |
+----+---------------+----------------+------------+
| 3  | another title | Lorem Ipsum... | 0          |
+----+---------------+----------------+------------+
| 4  | another title | Lorem Ipsum... | 0          |
+----+---------------+----------------+------------+

我尝试重新使用 WHERE 子句的别名 KW_MATCHED (请参阅下面的查询)，但它返回以下消息:

[Err] 1054 - Unknown column 'KW_MATCHED' in 'where clause'

SELECT
    A1.*, (
        SELECT
            sum(
                CASE
                WHEN (A1.ID = A2.tag_id)
                AND (
                    A2.keyword = 'keyword1' || A2.keyword = 'keyword2'
                ) THEN
                    1
                ELSE
                    0
                END
            )
        FROM
            tbl_article_tags A2
    ) AS KW_MATCHED
FROM
    tbl_article A1
WHERE
    KW_MATCHED > 0
ORDER BY
    KW_MATCHED DESC

我怎样才能正确地做到这一点？

Answer 1

尝试使用 HAVING 而不是 WHERE

SELECT
A1.*, 
(SELECT sum(case when (A1.ID=A2.tag_id) AND (A2.keyword='keyword1' || A2.keyword='keyword2') then 1 else 0 end) FROM tbl_article_tags A2) as KW_MATCHED
FROM tbl_article A1
HAVING KW_MATCHED > 0
ORDER BY KW_MATCHED DESC