MySQLi查询COUNT计数异常求助：点赞与浏览量数值互相叠加

你遇到的是多表JOIN产生的笛卡尔积问题：当同时LEFT JOIN likes和views两个表时，每条点赞记录会和每条浏览记录组合生成新的行。最终COUNT统计的是组合后的总行数，而非两个表各自的真实记录数——比如4条点赞+2条浏览，会生成8条组合行，所以两个COUNT结果都会变成8。

方案1：使用子查询单独统计（推荐） #

通过子查询分别从likes和views表中获取统计数据，彻底避免笛卡尔积的影响：

($stmt = $mysqli->prepare('
    SELECT `t`.`id`,`t`.`created`,`t`.`cat`,`t`.`quest`,`t`.`answer`,
    (SELECT COUNT(`id`) FROM `likes` WHERE `mod`="training" AND `task_id`=`t`.`id`) AS saves,
    (SELECT SUM(CASE WHEN `user_id`=? THEN 1 ELSE 0 END) FROM `likes` WHERE `mod`="training" AND `task_id`=`t`.`id`) AS saved,
    (SELECT COUNT(`id`) FROM `views` WHERE `mod`="training" AND `task_id`=`t`.`id`) AS views,
    (SELECT SUM(CASE WHEN `user_id`=? THEN 1 ELSE 0 END) FROM `views` WHERE `mod`="training" AND `task_id`=`t`.`id`) AS viewed
    FROM `training` `t`
    WHERE `t`.`id`= ? 
    LIMIT 1') or trigger_error($mysqli->error, E_USER_ERROR));
$stmt->bind_param('iii',$user['id'],$user['id'],$id);
$stmt->execute();
$stmt->bind_result($id, $date, $cat, $quest, $answer, $saves, $saved, $views, $viewed);
$stmt->store_result();
$stmt->fetch();

方案2：使用COUNT(DISTINCT)修正统计 #

如果不想改动JOIN结构，可以给COUNT加上DISTINCT关键字，只统计唯一的记录ID：

($stmt = $mysqli->prepare('
    SELECT `t`.`id`,`t`.`created`,`t`.`cat`,`t`.`quest`,`t`.`answer`,
    COUNT(DISTINCT `l`.`id`) AS saves, SUM(CASE WHEN `l`.`user_id`=? THEN 1 ELSE 0 END) AS saved,
    COUNT(DISTINCT `v`.`id`) AS views, SUM(CASE WHEN `v`.`user_id`=? THEN 1 ELSE 0 END) AS viewed
    FROM `training` `t`
    LEFT JOIN `likes` `l` ON (`l`.`mod`="training" AND `l`.`task_id`=`t`.`id`)
    LEFT JOIN `views` `v` ON (`v`.`mod`="training" AND `v`.`task_id`=`t`.`id`)
    WHERE `t`.`id`= ? 
    GROUP BY `t`.`id`
    LIMIT 1') or trigger_error($mysqli->error, E_USER_ERROR));
$stmt->bind_param('iii',$user['id'],$user['id'],$id);
$stmt->execute();
$stmt->bind_result($id, $date, $cat, $quest, $answer, $saves, $saved, $views, $viewed);
$stmt->store_result();
$stmt->fetch();

额外优化建议 #

为了避免重复点赞/浏览的无效数据，建议给likes和views表添加唯一约束：

• likes表：创建唯一键(mod, task_id, user_id)
• views表：创建唯一键(mod, task_id, user_id)

这样能确保同一个用户对同一条training记录只能点赞/浏览一次，从根源上保证统计结果的准确性。

内容的提问来源于stack exchange，提问作者who'sasking