mysql - 对连接表进行求和和计数

Question

没有遇到过这样的情况，所以不知道如何纠正。我猜需要一个子查询？

我需要 votes.vote 的总和以及 votes.vote 的计数。这使我能够计算所选位置的评级(所有投票的总和/投票数 = 评级)。

这里是带有 * 和静态绑定(bind)的查询，以便于理解:

//prepare
$stmt = $db->prepare("
    SELECT SQL_CALC_FOUND_ROWS
        *,
        COALESCE(SUM(votes.vote),0) AS vote_total,
        COUNT(votes.vote) AS number_votes
    FROM locations
    LEFT JOIN tables
        ON tables.location_id = locations.location_id
    LEFT JOIN votes
        ON votes.location_id = locations.location_id
    LEFT JOIN events
        ON events.location_id = locations.location_id
    WHERE locations.location_id = :location_id
    LIMIT :limit OFFSET :offset
");

//bindings
$binding = array(
    'location_id' => 11,
    'limit' => 20,
    'offset' => 0
);

//execute
$stmt->execute($binding);

//results
$results = $stmt->fetchAll(PDO::FETCH_ASSOC);

此位置已加入 11 个事件。只有两票加入其中(票数为 3 票，票数为 4 票)。我得到以下信息:

[vote_total] => 77 (should be 7, but is 7*11)
[number_votes] => 22 (should be 2, but is 2*11)

除此之外，仅返回一个结果而不是 11 个。如果我删除 votes 表连接并为其选择 SUM/COUNT，则所有 11 个结果都会按其应有的方式显示。

是否可以以某种方式在同一查询中获取 votes.vote 的 SUM 和 COUNT 总计，还是需要单独的查询来获取这些值？

Answer 1

根据您对问题的描述，我的最佳猜测是您希望每个事件一行。如果是这样:

SELECT SQL_CALC_FOUND_ROWS e.*
       COALESCE(SUM(v.vote), 0) AS vote_total,
       COUNT(v.vote) AS number_votes
FROM locations l LEFT JOIN
     tables
     ON t.location_id = l.location_id LEFT JOIN
     votes v
     ON v.location_id = l.location_id LEFT JOIN
     events e
     ON e.location_id = l.location_id
WHERE l.location_id = :location_id
GROUP BY e.event_id
LIMIT :limit OFFSET :offset;