MySQL查询返回过多的结果

Question

我正在处理一个项目，需要检查用户是否喜欢过帖子并使用COUNT()进行统计，如果返回0则表示没有喜欢过，返回1则表示已经喜欢过。

我尝试使用以下查询：

SELECT P.id AS id
    , U.username AS username
    , P.body AS body
    , P.timestamp AS timestamp
    , COUNT(L.user_id) AS likes
    , COUNT(LD.post_id) AS liked 
    FROM posts AS P 
    LEFT JOIN users AS U ON U.id = P.user_id 
    LEFT JOIN followers AS F ON F.user_id = 'user1' 
    LEFT JOIN likes AS L ON L.post_id = P.id 
    LEFT JOIN likes AS LD ON LD.post_id = P.id 
        AND LD.user_id = 'user1' 
    WHERE F.following_id = P.user_id 
        OR P.user_id = 'user1' 
    GROUP BY P.id

我喜欢表中的条目是

UserId|PostId|timestamp

user1 |post1 |time

user2 |post1 |time

我的问题是它一直给出2作为LD的计数，这不应该是可能的。

*注意：在我的代码中，我通过PDO使用：user，我实际上不会像那样键入ID。

编辑：

$sql = "SELECT P.id AS id, P.user_id AS userid, U.username AS username, U.name AS name, U.verified AS verified, P.body AS body, P.data AS data, P.timestamp AS timestamp, P.type AS type, P.users AS users, COUNT(L.user_id) AS likes, COUNT(DISTINCT LD.post_id) AS liked FROM posts AS P LEFT JOIN users AS U ON U.id = P.user_id LEFT JOIN followers AS F ON F.user_id = :userid LEFT JOIN likes AS L ON L.post_id = P.id LEFT JOIN likes AS LD ON LD.post_id = P.id AND LD.user_id = :userid WHERE F.following_id = P.user_id OR P.user_id = :userid GROUP BY P.id";
    $results = DB::query($sql, array(':userid' => $user_id));

我会循环遍历结果并将其格式化为json。

- Dan

---

你能展示所有涉及的表格的样本输入和期望输出吗？你当前的查询无效，因为你仅按帖子id分组，但是随后选择了其他非聚合列。 - Tim Biegeleisen

@TimBiegeleisen 我修改了代码的运行方式。我也不太理解 GROUP BY 的作用，我是否应该包括我选择的所有内容？ - Dan

当你告诉MySQL GROUP BY posts.id 时，你的意思是将属于每个 id 的所有记录聚合在一起，然后返回一些东西（例如MAX、AVG等）。因此，在查询中选择用户名毫无意义。恰巧MySQL容忍它，带来的副作用是不清楚你的查询尝试做什么。 - Tim Biegeleisen

@TimBiegeleisen 那我应该对除了 COUNT() 方法之外的所有选择使用 GROUP BY 吗？ - Dan

是的，这听起来很合理。除非您想要那些其他列的聚合，否则保持不变，但将每个其他列包装在MAX、AVG等中。 - Tim Biegeleisen

Answer 1

你能否尝试在 liked 列的 COUNT 函数中添加一个 DISTINCT 关键字？

COUNT(DISTINCT LD.post_id) AS liked

很可能是由于连接操作导致了 likes 表的重复。因此，我们将只使用 DISTINCT 计算唯一的帖子（根据 post_id）。

- KaeL