|
--========================================================
--ylb：Oracle
--17:13 2011-12-30
--1，子查询（嵌套子查询、相关子查询）
--========================================================
/***
连接与子查询的区别：
1，当需要多个表的数据时用连接，子查询只能返回单表数据。
2，连接快，子查询慢。
3，子查询功能强大。
4，子查询-两种（嵌套子查询，关联子查询）
嵌套简单，关联复杂，面试关联查询
**/
--一， 子查询第一种 : 嵌套子查询:简单--子查询可以独立运行，自内而外
--1，查询工资高于SMITH工资的所有员工
select * from emp where sal>(selectsal from emp where enAme='SMITH')
go
--2，查询工资高于公司平均工资的所有员工?
select * from emp where sal>(selectavg(sal) fromemp)
--附加题，
--> >= < <= = != <> ^= 后面只能跟一个值，
--如果有多个值，>all--大于最大值 >any--大于最小值
--查询工资高于所有部门的平均工资的员工
select * from emp where sal>all(selectavg(sal) fromemp group by deptno)
--查询工资高于任何部门的平均工资的员工
select * from emp where sal>any(selectavg(sal) fromemp group by deptno)
go
/*********************************************************************************/
--二， 子查询第二种 : 关联子查询 ,思考：自外而内
--3，查询工资高于本部门平均工资的所有员工?
select * from emp a where a.sal>(selectavg(sal) fromemp where deptno=a.deptno)
--4，查询本部门最高工资的员工?(三种方法)
--方法一，使用嵌套子查询（非关联子查询）
select * from emp a where (a.deptno,a.sal) in (selectdeptno,max(sal)from emp groupby deptno)
--方法二，使用关联子查询/*9-******************
select * from emp a where a.sal=(selectmax(sal) fromemp where deptno=a.deptno)
--方法三，使用关联子查询的名次问题，名次=人数+1
sal=800
deptno=20
select * from emp a
where (
select count(*) fromemp
where deptno=a.deptno and sal>a.sal)=1
/*********************************************************************************/
go
--补充题：
--查询本部门第二高工资的员工?(一种方法)
--5，查询本部门最低工资的员工 ?
select * from emp a where (selectcount(*) fromemp where deptno=a.deptno and sal<a.sal)=0
------------------------------------------------------三，select 语句做表达式
--6，统计每个部门的信息和人数?
select a.*,(select count(*)from emp wheredeptno=a.deptno) 人数 fromdept a
select a.* from dept a
select a.deptno,b.dname,b.loc,count(*)from emp a,dept bwhere a.deptno=b.deptnogroup bya.deptno,b.dname,b.loc
--7，统计每个部门工资在(500-1000)/(1000-3500)/(3500-7000) 的人数?
select a.*,
(selectcount(*) fromemp where deptno=a.deptno and sal>500 and sal<=1000)'500-1000',
(selectcount(*) fromemp where deptno=a.deptno and sal>1000 and sal<=3500),
(selectcount(*) fromemp where deptno=a.deptno and sal>3500 and sal<=7000)
from dept a
|
