用户表:(user)
id name age
1 张三 12
好友表:(friend) id name u_id 1 111 4
其中好友表中的u_id字段是 用户表id的一个外键。怎么求出 用户表中 好友最多的 前三个用户???
id name age
1 张三 12
好友表:(friend) id name u_id 1 111 4
其中好友表中的u_id字段是 用户表id的一个外键。怎么求出 用户表中 好友最多的 前三个用户???
select id,count(*)
from friend
group by id
) t order by 2 desc limit 3
mysql> select * from(
-> select id,count(*)
-> from friend
-> group by id
-> )order by desc limit 3;
ERROR 1248 (42000): Every derived table must have its own alias
select id,count(*)
from friend
group by id
) user order by 2 desc limit 3;
from user u join friend f
on
group by f.u_id
order by count(f.u_id) desc
limit 3;