MySQL - 依赖连接

Question

我正在尝试计算自由职业者的反馈并按如下方式排序:

$sql = "SELECT authentication.*, (SELECT COUNT(*) FROM freelanceFeedback) as taskscount FROM authentication
            LEFT JOIN freelanceFeedback
            ON authentication.userId=freelanceFeedback.FK_freelanceWinnerUserId
            WHERE `FK_freelanceProvider`=$what
            ORDER BY taskscount DESC";

但是如果用户有多个反馈并且没有按任务计数排序，我就会有多个输出。

我不明白“推文”有什么问题......

** 更新 **我想我自己已经明白了:

$sql = "SELECT DISTINCT authentication.*, 
            (SELECT COUNT(*) FROM freelanceFeedback
            WHERE FK_freelanceWinnerUserId=userId
            ) as taskscount 
            FROM authentication
            WHERE `FK_freelanceProvider`=$what
            ORDER BY taskscount DESC";

这里仅输出 1 个用户，并按反馈量排序。

Answer 1

使用COUNT()时，还需要使用GROUP BY:

    SELECT authentication.userId, 
           COUNT(freelanceFeedback.id) AS taskscount 
      FROM authentication
 LEFT JOIN freelanceFeedback
        ON authentication.userId = freelanceFeedback.FK_freelanceWinnerUserId
     WHERE `FK_freelanceProvider`= $what
  GROUP BY authentication.userId
  ORDER BY taskscount DESC

但是，只有当您不执行 SELECT * 时，这才有效(无论如何，这都是不好的做法)。所有不在 COUNT 位中的内容都需要进入 GROUP BY。如果这包括文本字段，您将无法执行此操作，因此您需要对子查询进行 JOIN。如果您不这样做，MySQL 不会提示，但它会严重减慢速度，并且其他数据库会抛出错误，因此最好正确执行:

    SELECT authentication.userId, 
           authentication.textfield, 
           authentication.othertextfield,
           subquery.taskscount
      FROM authentication
 LEFT JOIN (SELECT freelanceFeedback.FK_freelanceWinnerUserId,
                   COUNT(freelanceFeedback.FK_freelanceWinnerUserId) AS taskscount 
              FROM freelanceFeedback
          GROUP BY FK_freelanceWinnerUserId) AS subquery
        ON authentication.userId = subquery.FK_freelanceWinnerUserId
     WHERE authentication.FK_freelanceProvider = $what
  ORDER BY subquery.taskscount DESC

目前尚不清楚 FK_freelanceProvider 属于哪个表，因此我假设它是身份验证。