没有员工的公司: select * from user员工表 as a left join company公司表 as b on a.companyName = b.companyName where a.name is null有员工的公司 select * from user员工表 as a left join company公司表 as b on a.companyName = b.companyName where a.name is not null 或者 select * from user员工表 as a inner join company公司表 as b on a.companyName = b.companyName
select companyname,count(a.*) from user group by companyName order by companyName desc;
--有员工 select companyName from company ,(select companyName ,count(name) cn from user group by companyName) tt where tt.cn>1 and company.companyName=tt.companyName; --无员工 select companyName from company ,(select companyName ,count(name) cn from user group by companyName) tt where tt.cn=1 and company.companyName=tt.companyName;
select c.companyName,count(u.companyName) from company c,user u where c.companyName=u.companyName(+) group by c.companyName;
有员工的公司: select t.companyName from company t where exists (select 1 from user t1 where t1.companyName=t.companyName); 没有员工的公司: select t.companyName from company t where not exists (select 1 from user t1 where t1.companyName=t.companyName);
select * from user员工表 as a left join company公司表 as b on a.companyName = b.companyName
where a.name is null有员工的公司
select * from user员工表 as a left join company公司表 as b on a.companyName = b.companyName
where a.name is not null
或者
select * from user员工表 as a inner join company公司表 as b on a.companyName = b.companyName
--有员工
select companyName from company ,(select companyName ,count(name) cn from user group by companyName) tt where tt.cn>1 and company.companyName=tt.companyName;
--无员工
select companyName from company ,(select companyName ,count(name) cn from user group by companyName) tt where tt.cn=1 and company.companyName=tt.companyName;
select t.companyName from company t where exists (select 1 from user t1 where t1.companyName=t.companyName);
没有员工的公司:
select t.companyName from company t where not exists (select 1 from user t1 where t1.companyName=t.companyName);
我对各位的意见都分别进行了测试.Thank you !