多表查询2(8)

本文详细介绍了SQL查询中子查询的应用,包括如何使用子查询显示高于部门平均工资的员工信息,以及如何实现分页查询和合并查询。通过示例演示了使用内嵌视图进行复杂查询的方法,并对比了不同分页策略的执行效率。此外,还展示了如何利用SQL快捷命令创建新表,以及通过UNION、UNION ALL、INTERSECT和MINUS等操作符合并查询结果。本文旨在提供SQL查询技巧与实践的全面指南。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

在from字句中使用子查询
示例:如何显示高自己于部门平均工资的员工的信息
思路:先查出每个部门的平均工资
select deptno ,avg(sal) mysal from emp group by deptno;
然后在把查出的平均工资当作一个表
SQL> select a2.ename,a2.sal,a2.deptno,a1.mysal from emp a2,(selectdeptno,avg(sal) mysal from emp group by deptno) a1 where a2.deptno=a1.deptnoand a2.sal>a1.mysal;

要点:当在from字句中使用子查询时,该子查询会呗作为一个视图来对待,因此叫作内嵌视图,当在from字句中使用子查询时,必须给子查询指定别名,如上面下划线部分,别名为a1.(给表取别名不能加as给列取别名可以加as)

分页查询
示例:按雇员的id号升序取出,显示第十条到第六条之间的
SQL> select * from (select a1.*,rownum rn from (select * from emp) a1 whererownum<=10) where rn>=6;
其中:1.显示rownum[oracle分配的]2.指定查询列,只需修改最里层的子查询。3.如何排序,只需修改最里层的子查询。里面rownum自能出现一次如select a1.*,rownum rn from (select * from emp) a1 whererownum<=10 and rownum<=6;会出错。

分页分为三种
1.根据rowid来分
select * from t_xiaoxi where rowid in(select rid from (select rownum rn,ridfrom (select rowid rid,cid from t_xiaoxi order by cid desc) whererownum<10000)where rn>9980)order by cid desc;
执行时间0.03秒
2.按分析函数来分
select * from (select t.*,row_number() over(order by cid desc) rk from t_xiaoxit)where rownum<10000) where rn>9980;
执行时间1.01秒
3.按rownum来分
select * from(select t.*,rownum rn from (selec * from t_xiaoxi order by ciddesc) t where rownum<10000) where rn>9800;
z执行时间0.1秒
其中t_xiaoxi为表名称,cid为表的关键字段,取按cid降序排序后的第9981-9999条记录,t_xiaoxi表有700000多条记录
个人感觉1的效率最好,3次之,2最差

用查询结果创建新表
这个命令是一种快捷的建表方法
create table myemp (id,ename,sal) as select empno,ename,sal from emp;

合并查询
有时在集合操作符号实践应用中,为了合并多个select语句的结果,可以使用union,union all,intersect,minus
1).union
该操作符用于取得两个结果集的并集。当使用该操作符时,会自动去掉结果集中重复行。
示例:
单独查询
SQL> select ename,sal,job from emp where sal>2500;
ENAME             SAL JOB
---------- ---------- ---------
JONES            2975 MANAGER
BLAKE            2850 MANAGER
SCOTT            3000 ANALYST
KING             5000 PRESIDENT
FORD             3000 ANALYST

SQL>  select ename,sal,job from emp where job='MANAGER';
ENAME             SAL JOB
---------- ---------- ---------
JONES            2975 MANAGER
BLAKE            2850 MANAGER
CLARK            2450 MANAGER
合并查询
SQL> select ename,sal,job from emp where sal>2500 union
  2  select ename,sal,job from emp where job='MANAGER';
ENAME             SAL JOB
---------- ---------- ---------
BLAKE            2850 MANAGER
CLARK            2450 MANAGER
FORD             3000 ANALYST
JONES            2975 MANAGER
KING             5000 PRESIDENT
SCOTT            3000 ANALYST
已选择6行。

2).union all
该操作赋予union相似,但是它不会取消重复行,而且不会排序。
示例:
select ename,sal,job from emp where sal>2500
union all select ename,sal,job from emp where job='manager';
该操作符用于取得两个结果集的并集,当使用该操作符时,会自动去掉结果集中重复行。

3).intersect
使用该操作符用于取得两个结果集的交集
select ename,sal,job from emp where sal>2500 intersect
select ename,sal,job from emp where job='manager';

4).munus
使用该操作符用于取得两个结果集的差集,它只会显示存在第一个集合中,而不存在第二个集合中的数据。
select ename,sal,job from emp where sal>2500 minus

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值