连接表的总和和计数-Java 学习之路

没有遇到过这样的情况所以不确定如何纠正它 . 我猜测需要一个子查询？

我需要 votes.vote 的SUM和 votes.vote 的COUNT . 这允许我计算所选位置的评级（所有投票的总数/投票数=评级） .

这是带有*和静态绑定的查询，以便于理解：

//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总数，或者只需要单独的查询来获取这些值？

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;

回复于 2024-05-14T08:55:28+08:00

连接表的总和和计数

1 回答

相关问题