select 进阶查询语句

普通的 select…from 很明显不能满足我们的更细化的查询需求，它除了基本语法外，还可以拓展使用一些判断语法和过滤、分组语法。本文介绍一些 select 的进阶查询语法。其中包括等值连接、不等值连接、外连接（左外连接、右外连接）自连接、层次查询、子查询（相关子查询）等语法。本文所操作的均是 oracle 下 scott 用户下的表。大家可参考查阅。

---

等值连接

条件判断中使用=判断

select e.empno, e.ename, e.sal, d.dname
from emp e, dept d
where e.deptno = d.deptno
order by dname asc

不等值连接

条件判断中不使用=判断

select e.ename, e.empno, e.sal, s.grade
from emp e, salgrade s
where e.sal between s.losal and s.hisal;

外连接

希望在最后结果中，对于 where 条件来说不成立的记录，左外连接就是在表达式右侧增加(+)，相反，右外连接是在表达式左侧增加(+)。

左外连接：

select d.deptno, d.dname, count(e.ename)
from emp e, dept d
where d.deptno = e.deptno(+)
group by d.deptno, d.dname

右外连接：

select d.deptno, d.dname, count(e.ename)
from emp e, dept d
where e.deptno(+) = d.deptno
group by d.deptno, d.dname

自连接

将一张表通过别名的方式视为多张表来查询

select e.ename || '的老板是' || b.ename
from emp e, emp b
where e.mgr = b.empno

层次查询

用来解决处理比较大的数据时自连接导致笛卡尔集比较大的问题，应用场景比较多的就是树状结构的数据，比如城市地区列表等。

select level, empno, ename, mgr
from emp
connect by prior empno = mgr
start with mgr is null
order by 1;

子查询

子查询其实就是嵌套 select 语句查询，嵌套的 select 要用小括号括起来，子查询可以写在 select 后面，也可以写在 from 后面，也可以写在 where 后面，但是写在不同的位置都有不同的规则，在 oracle 中子查询是非常需要得到重视的，所以子查询有如下十条规则。
1、合理的书写风格，该换行的地方换行，该缩进的地方缩进
2、小括号
3、主查询和子查询可以是不同的表，只要子查询出来的结果主查询可用就行了
4、可以在主查询的 where、selcet、having、from 后面都可以放子查询
5、group by 后面不能放子查询
6、from后面放置的子查询（***），from后面放置的是一个集合（表，查询结果）
7、一般情况下先进行子查询（内查询），再进行主查询（外查询）但是相关子查询除外
8、一般情况下不在子查询中使用order by（排序没有太大意义）Top-N必须使用 order by
9、单行子查询只能使用单行操作符，多行子查询只能使用多行操作符
10、子查询中null值

单行子查询案例：

select * from emp where sal > (select sal from emp where ename = 'SCOTT');

多行子查询案例：

查询部门名称为SALES和ACCOUNTING的员工信息，其中 in 语法表示等于条件中的任意一个都成立

select * from emp
where deptno in
   (
           select deptno
           from dept
           where dname = 'SALES'
           or dname = 'ACCOUNTING'
   );

查询比30号部门任意一个员工公司高的员工，其中 any 语法表示和集合中的任意一个值比较符合条件就可以

select * from emp
where sal > any
   (
           select sal
           from emp
           where deptno = 30
   );

查询比30号部门所有人工资都高的人，其中 all 语法表示和集合内的所有值比较符合条件才可以

select * from emp
where sal > all
   (
           select sal
           from emp
           where deptno = 30
   )

相关子查询

相关子查询就是主查询将某个值作为参数传递给子查询，请参考后面练习题第二题。

Oracle 分页

在 web 应用中，经常会对数据库有分页的需求，Oracle 不像其他数据库，有非常简单的分页函数，只能通过其提供的一个伪列 rownum 来实现。看如下例子。

select * from
   (
           select rownum r, empno, ename, sal from emp
   )
where r >= 5 and r <= 10

习题

1、求员工中所有人工资排前3名的人员

select * from
   (
           select rownum, empno, ename, sal
           from emp
           order by sal desc
   )
where rownum <= 3

2、查找员工表中薪水大于本部平均薪水的员工

方法1：

select empno, ename, sal, s.avgsal
from emp, (select deptno, avg(sal) avgsal
		from emp
		group by deptno) s
where s.deptno = emp.deptno
and emp.sal > s.avgsal

方法2，使用相关子查询

select empno, ename, sal, 
	(
		select avg(sal)  
		from emp
		where deptno=e.deptno
	) avgsal
	from emp e
	where sal > 
		(
			select avg(sal)
			from emp
			where deptno=e.deptno
		)

查询每年入职员工人数

select  count(*) Total,
   sum(decode(to_char(hiredate, 'yyyy'), '1980', 1, 0)) as "1980",
   sum(decode(to_char(hiredate, 'yyyy'), '1981', 1, 0)) as "1981",
   sum(decode(to_char(hiredate, 'yyyy'), '1982', 1, 0)) as "1982",
   sum(decode(to_char(hiredate, 'yyyy'), '1987', 1, 0)) as "1987"
from emp