即使使用DISTINCT，MYSQL也会加入重复的结果

Question

I have 2 mysql tables one called 'users' and another called 'connections'

Table users have info about users and has 3 rows:

mikha
guy
maricela

Table connections has the connections between users like twitter (mikha following maricela and maricela following mikha for example)

Connections has the following rows:

username1     | username2
--------------------------
guy           | maricela
mikha         | guy
mikha         | maricela

I want to get info about 'mikha' including the number of people he following and the number who follow him back.

I use the following query:

SELECT *, COUNT(DISTINCT  connections.username1) AS count_following, 
   COUNT(DISTINCT connections.username2)  AS count_followers 
FROM users LEFT JOIN connections on connections.username1 = 'mikha' OR  
   connections.username2 = 'mikha' WHERE users.username = 'mikha'

Expected:

count_following = 2 (as mikha is following guy and maricela)
count_followers = 0 (no one following mikha yet)

Actual:

count_following = 2
count_followers = 1

Thanks Regards Michael

Answer 1

You can run two different queries (fiddle here):

select
  (select count(*) from connections
  where username1 = 'mikha') following,
  (select count(*) from connections
  where username2 = 'mikha') followers

Or just use this (fiddle here):

select
    sum(username1 = 'mikha') following,
    sum(username2 = 'mikha') followers
from connections

I think the second one should be faster. Once you get that just join with your users table in case you need extra info from that table (given your example, you don't).