在ZF2查询中求和整数值

I have a query which basically picks out all of the points in my mysql table. I'm using the Zend Framework 2 and the query basically looks like this:

我有一个查询基本上挑选了我的mysql表中的所有点。我正在使用Zend Framework 2，查询基本上如下所示：

public function GetFiveUsersWithHighestPoints($brandId,$startDate, $endDate)
    {
        $select = new Select();
        $select->from(array("h" => $this->table));
        $select->columns(array(
            "id",
            "userId",
            "type",
            "points",
            "missionId",
            "rewardId",
            "brandId",
            "goodsId",
            "time",
        ));
        $select->order("h.points desc");
        $where = new Where();
        $select->join(array("m" => "missions"), "h.missionId = m.id", array("missionId" => "id", "missionName" => "title", "missionAction" => "action"), Select::JOIN_LEFT);
        if (!empty($journeyId)) {
            $where->equalTo("m.journeyId", $journeyId);
        } else {
            $select->join(array("r" => "rewards"), "h.rewardId = r.id", array("rewardId" => "id", "rewardName" => "name"), Select::JOIN_LEFT);
        }
        $where->notEqualTo("h.points", 0);
        $where->equalTo("h.brandId", $brandId);
        $where->between("h.time", $startDate, $endDate);
        //分页代码块。固定用法
        $select->where($where);
        return $this->historyTable->selectWith($select)->toArray();
    }

Now what I would like to do is that all of the points are grouped by userid, which would basically correspond to the fact how many points each user has made in the given time span... How can I do sum all of the points per UserId, so that I get something like this:

现在我想要做的是所有的点都按用户ID分组，这基本上对应于每个用户在给定时间跨度内有多少点...我怎样才能将所有点数相加UserId，所以我得到这样的东西：

UserId 26 => 100 points
UserId 26 => 100分
UserId 27 => 200 points
UserId 27 => 200分

etc. etc.

等等

I'm quite new to writing queries in Zend Framework 2 and I'm not sure what am I supposed to do next...

我很想在Zend Framework 2中编写查询，我不确定接下来该做什么...

1 个解决方案

#1

How about adding a SUM() column with a GROUP BY, something like:

如何使用GROUP BY添加SUM（）列，如：

use Zend\Db\Sql\Expression;

// Include a SUM() expression
$select->cols([

    // Include existing columns
    // etc...

    // Add a SUM col
    'pointsCount' => new Expression('SUM(h.points)'),

]);

// Add a GROUP BY
$select->group('h.userId');

The per-user sum of points that you seek should then accessible in each result row under the name pointsCount.

然后，您可以在名称pointsCount下的每个结果行中访问您要搜索的每个用户点数。

#1