子查询:嵌套在sql中的另一个查询,一般来讲子查询以括号“()“,放在一个大查询中
简单子查询
子查询与父查询之间,没有必然的联系
例:查询工资和scott 一样的记录
select * from scott.emp
where sal=(select sal from scott.emp where ename='SCOTT');
例:查询工资高于公司平均工资的记录
select * from scott.emp where sal>(select avg(sal) from scott.emp);
关联子查询
子查询与父查询间有一定的联系,这类查询一般处理的方法:在子查询中引用父查询数据,
必要的时候加别名
例:查询工资高于部门平均工资的记录
select * from scott.emp e
where sal>(select avg(sal) from scott.emp where deptno=e.deptno);
例:查询哪些人在少于5人的部门工作
SQL> select * from scott.emp
where deptno in
(select deptno from scott.emp
group by deptno having count(*) < 6);
单行子查询与多行子查询
单行子查询即子查询只返回一行数据,多行子查询子查询中可能会返回多行数据,
它们的区别主要是在比较符的上区别
单行子查询的比较符:
> >= < <= = <>
多行比较符:
in
>any (... ) >=any (...)
>all (... ) >=all (...)
多列子查询
select ... from ...
where (a,b) in (select a,b)
如上,同时比较两个列,但要注意的是子查询中的列数要和前面列的个数一致
象这些查询都可以转换为多条件的,如上面的查询可改写成
select ... from ...
where a in (... ) and b in ()
子查询相关的一些应用:
1、去重
SQL> create table a (a number(4),b number(5));
SQL> insert into a values (1,2) ;
SQL> insert into a values (2,2);
SQL> insert into a values (2,3);
SQL> insert into a values (1,2);
SQL> insert into a values (2,3);
SQL> commit;
SQL> select * from a;
A B
---------- ----------
1 2
2 2
2 3
1 2
2 3
现在把重复的记录删除,只保离一条
SQL> delete from a where rowid
not in (select max(rowid) from a group by a,b);
2、在某些排序中
例:查询scott.emp 中工资最高的前三个
SQL> select * from
(select * from scott.emp order by sal desc) where rownum<=3;
查工资最高 3-5 名
SQL> select * from
(select rownum rn,t.* from
(select * from scott.emp order by sal desc) t)
where rn>=3 and rn<=5;
SQL> select * from
2 (select * from scott.emp order by sal desc) where rownum<=5
3 minus
4 select * from
5 (select * from scott.emp order by sal desc) where rownum<=2;
with
在下些查询中可能会反复用到某些语句,为了书写的方便,可以将这些代码起个名字
以后引用即可
SQL> with t as
2 (select * from scott.emp order by sal desc)
3 select * from t where rownum<=5
4 minus
5 select * from t where rownum<=2;
简单子查询
子查询与父查询之间,没有必然的联系
例:查询工资和scott 一样的记录
select * from scott.emp
where sal=(select sal from scott.emp where ename='SCOTT');
例:查询工资高于公司平均工资的记录
select * from scott.emp where sal>(select avg(sal) from scott.emp);
关联子查询
子查询与父查询间有一定的联系,这类查询一般处理的方法:在子查询中引用父查询数据,
必要的时候加别名
例:查询工资高于部门平均工资的记录
select * from scott.emp e
where sal>(select avg(sal) from scott.emp where deptno=e.deptno);
例:查询哪些人在少于5人的部门工作
SQL> select * from scott.emp
where deptno in
(select deptno from scott.emp
group by deptno having count(*) < 6);
单行子查询与多行子查询
单行子查询即子查询只返回一行数据,多行子查询子查询中可能会返回多行数据,
它们的区别主要是在比较符的上区别
单行子查询的比较符:
> >= < <= = <>
多行比较符:
in
>any (... ) >=any (...)
>all (... ) >=all (...)
多列子查询
select ... from ...
where (a,b) in (select a,b)
如上,同时比较两个列,但要注意的是子查询中的列数要和前面列的个数一致
象这些查询都可以转换为多条件的,如上面的查询可改写成
select ... from ...
where a in (... ) and b in ()
子查询相关的一些应用:
1、去重
SQL> create table a (a number(4),b number(5));
SQL> insert into a values (1,2) ;
SQL> insert into a values (2,2);
SQL> insert into a values (2,3);
SQL> insert into a values (1,2);
SQL> insert into a values (2,3);
SQL> commit;
SQL> select * from a;
A B
---------- ----------
1 2
2 2
2 3
1 2
2 3
现在把重复的记录删除,只保离一条
SQL> delete from a where rowid
not in (select max(rowid) from a group by a,b);
2、在某些排序中
例:查询scott.emp 中工资最高的前三个
SQL> select * from
(select * from scott.emp order by sal desc) where rownum<=3;
查工资最高 3-5 名
SQL> select * from
(select rownum rn,t.* from
(select * from scott.emp order by sal desc) t)
where rn>=3 and rn<=5;
SQL> select * from
2 (select * from scott.emp order by sal desc) where rownum<=5
3 minus
4 select * from
5 (select * from scott.emp order by sal desc) where rownum<=2;
with
在下些查询中可能会反复用到某些语句,为了书写的方便,可以将这些代码起个名字
以后引用即可
SQL> with t as
2 (select * from scott.emp order by sal desc)
3 select * from t where rownum<=5
4 minus
5 select * from t where rownum<=2;
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/21175589/viewspace-755562/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/21175589/viewspace-755562/