mysql里有两个表,member(id,memberName)和record(id,memberId)memberId就是member表里id
我的目的是选出 member表里的全部memberName,然后按record表里的记录数来排序,我写了下面这个语句,但我发现一个很大的漏洞就是如果这个memberName在record表里无记录的话,那也就不能取出了,有没有别的什么办法写这样一个sql,把member里的所有记录都取出来,当然要按record里的记录多少,由高到低的排序select a.memberName,count(*) from member a,record b
where a.id = b.memberId
group by a.memberName
order by count(*) desc
我的目的是选出 member表里的全部memberName,然后按record表里的记录数来排序,我写了下面这个语句,但我发现一个很大的漏洞就是如果这个memberName在record表里无记录的话,那也就不能取出了,有没有别的什么办法写这样一个sql,把member里的所有记录都取出来,当然要按record里的记录多少,由高到低的排序select a.memberName,count(*) from member a,record b
where a.id = b.memberId
group by a.memberName
order by count(*) desc
left join record b
on a.id = b.memberId
group by a.memberName
order by count(*) desc
select a.id,a.memberName,count(b.id) as total_count
from member a
left join record b
on a.id = b.memberId
group by a.id
order by total_count desc
from member a
left join record b
on a.id = b.memberId
group by a.id,a.memberName
order by total_count desc
Query OK, 0 rows affected (0.05 sec)mysql> create table record(id int,memberId int);
Query OK, 0 rows affected (0.06 sec)mysql> select a.id,a.memberName,count(b.id) as total_count
-> from member a
-> left join record b
-> on a.id = b.memberId
-> group by a.id
-> order by total_count desc;
Empty set (0.01 sec)mysql>从你上面提示的错误看, 应该是你拷贝时换行符那里出错了
select a.memberName,count(b.id) from member a
left join record b
on a.id = b.memberId
group by a.memberName
order by count(b.id) asc