select ename,sal from emp where
sal > (select avg(sal) from emp);
按照分组之后每个部门工资做多的
select ename,sal,deptno from emp
where sal = (select max(sal) from emp group by deptno); //错误单行子查询返回多行
select ename,sal,deptno from emp
where sal in (select max(sal) from emp group by deptno); //错误,in是指3个组max值之一
正确的
select max(sal),deptno from emp group by deptno;
MAX(SAL) DEPTNO
--------- ----------
5000 10
3000 20
2850 30
连接上面这张查出来的表 表连接
select ename,sal from emp
join (select max(sal) max_sal,deptno from emp group by deptno) t
on (emp.sal=t.max_sal and emp.deptno = t.deptno);
ENAME SAL
---------- ----------
BLAKE 2850
SCOTT 3000
KING 5000
FORD 3000
练习 求每个部门平均薪水后,告诉我这个薪水等级 ????????????????
求一个人的名字和经理人的名字
select empno,ename,magr from emp;
EMPNO ENAME MGR
7369 SMITH 7902
7499 ALLEN 7698
7521 WARD 7698
7566 JONES 7839
7654 MARTIN 7698
7698 BLAKE 7839
7782 CLARK 7839
7788 SCOTT 7566
7839 KING
7844 TURNER 7698
7876 ADAMS 7788
7900 JAMES 7698
7902 FORD 7566
7934 MILLER 7782
select e1.ename,e2.ename from emp e1,emp e2 where e1.mgr=e2.empno;
ENAME MGRNAME
FORD JONES
SCOTT JONES
JAMES BLAKE
TURNER BLAKE
MARTIN BLAKE
WARD BLAKE