[子查询(嵌套查询): 将某个查询作为再查询的数据来源。即:将某个查询作为一张表来看待。]
1、在emp表中,哪个员工的工资最高。
SQL>select ename, sal from emp
where sal = (select max(sal) from emp );
2、在emp表中,哪些员工的工资高于平均工资。
SQL>select ename, sal from emp
where sal > (select avg(sal) from emp );
3、在emp表中,各部门中哪个员工的工资最高。
[错了,原因: 子查询中有多个结果。]
SQL>select ename, sal, deptno from emp
where sal = (select max(sal) from emp group by deptno);
[因此,不能使用等号,而因使用包含运算符in,得到结果此题恰好正确;
但是,当10部门也有一个3000元工资的员式也会被找出。那,这就错了。]
SQL>select ename, sal, deptno from emp
where sal in (select max(sal) from emp group by deptno);
[说明: 以上语句在达到目的上是有漏洞的。]
先按部门分组找出各部门中最高的工资,并将该查询作为一张表看待。
SQL>select max(sal), deptno from emp group by deptno;
再将此表进行连接,即可查出无漏洞的结果
SQL>select ename, sal,dt.deptno from emp
join
(select deptno , max(sal) max_sal from emp
group by deptno) dt
on
(emp.deptno=dt.deptno and emp.sal = dt.max_sal );
[其中join表示连接某表,on是按指条件进行连接之意]
4、每个部门的平均工资所属的等级。
[答案]
SELECT deptno,round(avg_sal,2), grade FROM salgrade
join
(SELECT deptno,avg(sal) avg_sal FROM emp GROUP BY deptno) tmp
on
tmp.avg_sal BETWEEN losal AND hisal ;
[或]
SELECT deptno,round(avg_sal,2), grade FROM
(SELECT deptno,avg(sal) avg_sal FROM emp
GROUP BY deptno) tmp,salgrade
WHERE tmp.avg_sal BETWEEN losal AND hisal ORDER BY grade DESC;
[分解]
1、计算各部门的平均工资。
SELECT deptno,avg(sal) avg_sal FROM emp GROUP BY deptno
2、计算结果与工资等级表salgrade做连接,计算出等级grade,并由大到小排序。
SELECT deptno,grade,avg_sal
FROM (SELECT deptno,avg(sal) avg_sal
FROM emp GROUP BY deptno) tmp,salgrade
WHERE tmp.avg_sal BETWEEN losal AND hisal ORDER BY grade DESC;
1、在emp表中,哪个员工的工资最高。
SQL>select ename, sal from emp
where sal = (select max(sal) from emp );
2、在emp表中,哪些员工的工资高于平均工资。
SQL>select ename, sal from emp
where sal > (select avg(sal) from emp );
3、在emp表中,各部门中哪个员工的工资最高。
[错了,原因: 子查询中有多个结果。]
SQL>select ename, sal, deptno from emp
where sal = (select max(sal) from emp group by deptno);
[因此,不能使用等号,而因使用包含运算符in,得到结果此题恰好正确;
但是,当10部门也有一个3000元工资的员式也会被找出。那,这就错了。]
SQL>select ename, sal, deptno from emp
where sal in (select max(sal) from emp group by deptno);
[说明: 以上语句在达到目的上是有漏洞的。]
先按部门分组找出各部门中最高的工资,并将该查询作为一张表看待。
SQL>select max(sal), deptno from emp group by deptno;
再将此表进行连接,即可查出无漏洞的结果
SQL>select ename, sal,dt.deptno from emp
join
(select deptno , max(sal) max_sal from emp
group by deptno) dt
on
(emp.deptno=dt.deptno and emp.sal = dt.max_sal );
[其中join表示连接某表,on是按指条件进行连接之意]
4、每个部门的平均工资所属的等级。
[答案]
SELECT deptno,round(avg_sal,2), grade FROM salgrade
join
(SELECT deptno,avg(sal) avg_sal FROM emp GROUP BY deptno) tmp
on
tmp.avg_sal BETWEEN losal AND hisal ;
[或]
SELECT deptno,round(avg_sal,2), grade FROM
(SELECT deptno,avg(sal) avg_sal FROM emp
GROUP BY deptno) tmp,salgrade
WHERE tmp.avg_sal BETWEEN losal AND hisal ORDER BY grade DESC;
[分解]
1、计算各部门的平均工资。
SELECT deptno,avg(sal) avg_sal FROM emp GROUP BY deptno
2、计算结果与工资等级表salgrade做连接,计算出等级grade,并由大到小排序。
SELECT deptno,grade,avg_sal
FROM (SELECT deptno,avg(sal) avg_sal
FROM emp GROUP BY deptno) tmp,salgrade
WHERE tmp.avg_sal BETWEEN losal AND hisal ORDER BY grade DESC;