小弟新人 基本都不怎么懂 向各位大哥请教
create table emp
(
empno varchar(10) primary key,
ename varchar(10),
job varchar(10),
mgr varchar(10),
sal float,
deptno varchar(10) references dept(deptno)
);
insert into emp values ('01','jacky','clerk','tom','1000','1');
insert into emp values ('02','tom','clerk','','2000','1');
insert into emp values ('07','biddy','clerk','','2000','1');
insert into emp values ('03','jenny','sales','pretty','600','2');
insert into emp values ('04','pretty','sales','','800','2');
insert into emp values ('05','buddy','jishu','canndy','1000','3');
insert into emp values ('06','canndy','jishu','','1500','3');
--13对emp中低于自己工资至少5人的员工，列出其部门号，姓名，工资，以及工资少于自己的人数
求解怎么用连接查询得到结果
有个参考答案是子查询，但我怎么也想不出来怎么用join on查询等同于这个子查询
子查询答案select a.deptno as 部门号,a.ename as 姓名,a.sal as 工资,(select count(b.ename) from emp as b
where b.sal<a.sal) as 人数
from emp as a
where (select count(b.ename) from emp as b where b.sal<a.sal)>=5
------解决方案--------------------
select a.deptno,a.ename,a.sal,COUNT(*) 人数 from
emp a join emp b on a.sal>b.sal
group by a.deptno,a.ename,a.job,a.sal
having COUNT(*)>=5