mysql - 在 MySQL 中使用带有 HAVING 子句的 COUNT

Question

我们在弄清楚为什么在语句末尾使用 HAVING 子句时不能 COUNT 时遇到了一些麻烦。

此语句返回结果:

SELECT lid, cid, title, url, description, date, hits, downloadratingsummary, totalvotes, totalcomments, filesize, version, homepage, ns_compat, ns_des_img, ( (2.0 * (MATCH(title) AGAINST ('fsx concorde' IN BOOLEAN MODE))) + (1.0 * (MATCH(description) AGAINST ('fsx concorde' IN BOOLEAN MODE))) + (0.5 * (MATCH(url) AGAINST ('fsx concorde' IN BOOLEAN MODE))) ) AS relevance FROM nuke_downloads_downloads WHERE ( MATCH(title,description,url) AGAINST ('fsx concorde' IN BOOLEAN MODE) ) HAVING relevance > 3.0 ORDER BY relevance DESC

这条语句不返回任何内容

SELECT COUNT(*) as num, lid, cid, title, url, description, date, hits, downloadratingsummary, totalvotes, totalcomments, filesize, version, homepage, ns_compat, ns_des_img, ( (2.0 * (MATCH(title) AGAINST ('fsx concorde' IN BOOLEAN MODE))) + (1.0 * (MATCH(description) AGAINST ('fsx concorde' IN BOOLEAN MODE))) + (0.5 * (MATCH(url) AGAINST ('fsx concorde' IN BOOLEAN MODE))) ) AS relevance FROM nuke_downloads_downloads WHERE ( MATCH(title,description,url) AGAINST ('fsx concorde' IN BOOLEAN MODE) ) HAVING relevance > 3.0 ORDER BY relevance DESC

我们是否遗漏了什么？

非常感谢您的帮助，并提前致谢。

Answer 1

如果您只需要第一个查询返回的行数，您可以将查询封装到派生表中。

除了 SELECT 列表中的 relevance 和 ORDER BY 之外，您可以跳过所有列:

SELECT COUNT(*)
FROM
  ( SELECT ( (2.0 * (MATCH ... ) AS relevance 
    FROM ...                                     -- unchanged
    WHERE ...                                    -- unchanged
    HAVING relevance > 3.0
                                                 -- ORDER BY removed    
  ) AS t ;