Mysql:如何仅在列(已接受)= 1 时选择？

Question

这是我的代码，它显示正确，除非我想将它限制为仅从 friend 表中过滤“已接受”=“1”……截至目前，它显示在所有地方

SELECT users.id, users.username, users.avatar, COUNT(friends.accepted) AS friends 
FROM users
  LEFT JOIN friends ON friends.user1=users.username || friends.user2=users.username
GROUP BY users.username
ORDER BY friends DESC LIMIT 20

Answer 1

如果你只想得到一个接受了 friend 的人，使用HAVING (为简单起见，HAVING 等同于 WHERE，用于过滤聚合结果):

SELECT users.id, users.username, users.avatar, COUNT(*) AS friends 
FROM users
LEFT JOIN friends ON friends.user1=users.username || friends.user2=users.username
GROUP BY users.username
HAVING COUNT(friends.accepted) = 1
ORDER BY friends DESC LIMIT 20

如果你想计算 accepted = 1 的 friend ，一个简单的 WHERE 应该可以工作:

SELECT users.id, users.username, users.avatar, COUNT(*) AS friends 
FROM users
LEFT JOIN friends ON friends.user1=users.username || friends.user2=users.username
WHERE friends.accepted = 1
GROUP BY users.username
ORDER BY friends DESC LIMIT 20

或者，如果您还想列出没有 friend 的人，其中 accepted = 1:

SELECT users.id, users.username, users.avatar, 
    SUM(CASE friends.accepted = 1 THEN 1 ELSE 0 END) AS friends 
FROM users
LEFT JOIN friends ON friends.user1=users.username || friends.user2=users.username
GROUP BY users.username
ORDER BY friends DESC LIMIT 20