用right join select B.name,count(*) from team A right join category b on A.cityid=B.id group by B.name
select name,count(team.id) from category left join team on category.id=team.id group by name
create table team(id int,title varchar(10),city_id int,); insert into team select 1,'测试',1 union all select 2,'测试',2 union all select 3,'测试',3 union all select 4,'测试',1 union all select 5,'测试',1 union all select 6,'测试',2 ;create table category(id int,name varchar(10)); insert into category select 1,'湖南' union all select 2,'深圳' union all select 3,'广州' union all select 4,'杭州' union all select 5,'东北' union all select 6,'北京' ;select name,count(a.city_id) from team a right join category b on a.city_id=b.id group by b.name order by count(a.city_id) desc -------------- --湖南 3 --深圳 2 --广州 1 --杭州 0 --北京 0 --东北 0
select B.name,count(*) from team A right join category b on A.cityid=B.id group by B.name
select B.name,count(*)
from team A right join category b on A.cityid=B.id
group by B.name
from category left join team on category.id=team.id
group by name
insert into team
select 1,'测试',1 union all
select 2,'测试',2 union all
select 3,'测试',3 union all
select 4,'测试',1 union all
select 5,'测试',1 union all
select 6,'测试',2 ;create table category(id int,name varchar(10));
insert into category
select 1,'湖南' union all
select 2,'深圳' union all
select 3,'广州' union all
select 4,'杭州' union all
select 5,'东北' union all
select 6,'北京' ;select name,count(a.city_id)
from team a right join category b on a.city_id=b.id
group by b.name
order by count(a.city_id) desc
--------------
--湖南 3
--深圳 2
--广州 1
--杭州 0
--北京 0
--东北 0