mysql - 缺少在F3中使用SQL的HAVING子句的选项

有没有一种方法可以将MySQL的HAVING子句与任何Fat Free Framework's SQL Mapper object's方法一起使用？假设我有以下数据库表：

+----+-------+--------+
| id | score | weight |
+----+-------+--------+
|  2 |     1 |      1 |
|  2 |     2 |      3 |
|  2 |     3 |      1 |
|  2 |     2 |      2 |
|  3 |     1 |      4 |
|  3 |     3 |      1 |
|  3 |     4 |      3 |
+----+-------+--------+

现在，我想运行以下查询：

SELECT id, SUM(score*weight)/SUM(weight) AS weighted_score GROUP BY id HAVING weighted_score>2

实话实说，我实际上想计算这些记录的数量，但是count方法不支持$options。

我可以在没有HAVING子句的情况下运行查询，然后遍历它们以对weighted_score的值进行检查，但是随着记录的增加，它将越来越消耗资源。是否有内置解决方案来解决此问题？

编辑1：
如果不支持HAVING子句（基于manual），我知道该怎么做：

$databaseObject = new DB\SQL(...);
$dataMapper = new \DB\SQL\Mapper($databaseObject, "tableName");
$dataMapper->weightedScore = "SUM(weight*score)/SUM(weight)";
$usersInfo = $dataMapper->find([],["group"=>"id"]);
$place = 1;
foreach ( $usersInfo as $userInfo ) {
    if ( $usersScores->weightedScore > 2) $place++;
}

如果我能够使用HAVING子句，则不需要foreach循环，并且可以减少查询加载的项目数：

$databaseObject = new DB\SQL(...);
$dataMapper = new \DB\SQL\Mapper($databaseObject, "tableName");
$dataMapper->weightedScore = "SUM(weight*score)/SUM(weight)";
$usersInfo = $dataMapper->find([],["group"=>"id", "having"=>"weighted_score<2"]); // rough idea
$place = count($usersInfo);

如果count method支持$options，它将更加简单，并且因为没有记录会加载，因此可以节省应用程序使用的内存：

$databaseObject = new DB\SQL(...);
$dataMapper = new \DB\SQL\Mapper($databaseObject, "tableName");
$dataMapper->weightedScore = "SUM(weight*score)/SUM(weight)";
$place = $dataMapper->count([],["group"=>"id", "having"=>"weighted_score<2"]); // rough idea

最佳答案

使用子查询。

select count (0) from (SELECT id, SUM(score*weight)/SUM(weight) AS weighted_score GROUP BY id) where weighted_score>2;

希望它会有所帮助。

关于mysql - 缺少在F3中使用SQL的HAVING子句的选项，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/39792443/