如何从朋友表中获取朋友列表,其中包含我朋友的朋友计数(不包括我朋友的计数)
好友表"tbl_users_friends
字段1:id字段2:user_id字段3:friend_user_id
我需要输出为:
A有以下好友:
x(10)y(2)z(0)
上面是我的朋友列表,括号中包含了他们的朋友数。
感谢
select user_id, count(*) cnt
from Friends
where user_id in
(select friend_user_id
from Friends
where user_id = user_id_of_A)
group by user_id
试试这样的东西:
select u.user_id, u.name, count(uf1.id) as num_friends
from tbl_users_friends uf
inner join tbl_users u on u.user_id = uf.friend_user_id
left join tbl_users_friends uf1 on uf1.user_id = uf.friend_user_id
where uf.user_id = 1
group by u.user_id, u.name
http://sqlfiddle.com/#!9/10033/1
您需要调整users表和列名。
另一个有子选择但没有分组的解决方案:
select u.user_id, u.name, (
select count(*)
from tbl_users_friends uf1
where uf1.user_id = uf.friend_user_id
) as num_friends
from tbl_users_friends uf
inner join tbl_users u on u.user_id = uf.friend_user_id
where uf.user_id = 1