mysql - 在MySQL中将COUNT与HAVING子句一起使用

在语句末尾使用having子句时，我们很难理解为什么不能计数。
此语句返回结果：

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

我们有什么遗漏吗？
非常感谢您的帮助，并提前向您表示感谢。

最佳答案

如果只需要第一个查询将返回的行数，则可以将查询填充到派生表中。
您可以跳过除relevance列表中的SELECT和ORDER BY之外的所有列：

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

关于mysql - 在MySQL中将COUNT与HAVING子句一起使用，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/18918248/