mysql数据查询
数据表user
id（用户表ID） uid（谁邀请了这个用户） nickname（昵称） password（密码）
1 0 nihao *****
2 1 ceshi *****
3 1 ceshi2 ******
4 3 hello *****
.
.
.
.
.
.
这样的表，现在我想查id，nickname还有每个用户的邀请数nid（比如id为1的这个用户邀请了几个用户），sql语句怎么写？
因为涉及到nid 的排序，所以我不知道该怎么写，求指教！！！
select id,nickname,case when b.nid is null then 0 else b.nid end as nid
from user as a left join (select uid,count(*) as nid from user group by uid) as b on a.id=b.uid
order by b.nid desc
select a.id,a.nickname,b.useramount from user a left join (select uid,count(*) as useramount from user
group by uid ) b on a.id=b.uid;
-
SQL code
SELECT ot.id,ot.nickname,
(SELECT COUNT(*) FROM user ine WHERE ine.uid=ot.id) AS uid_counts
FROM user ot
ORDER BY uid_counts DESC
