Oracle_Day2 查询练习,以及分页

1.伪列:rownum:

ROWNUM注意点:

(1)按照默认的顺序生成(就是没有排序的)

(2)ROWNUM只能使用<或<=,不能使用>或>=。(经过测试发现>或>=1是可以查询出结果的,但是1以上就不能查询出结果了)

查询出工资前3的员工信息

SELECT ROWNUM,ENAME,SAL FROM
EMP ORDER BY SAL DESC

进行排序后发现ROWNUM顺序是错误的。

如果希望ROWNUM排列正确的话,那么可以在外面再套一层ROWNUM的查询语句。注意ORDER BY要写在子查询里,要不然顺序还是乱的。

SELECT ROWNUM,ENAME,SAL FROM
(SELECT ROWNUM,ENAME,SAL FROM
EMP ORDER BY SAL DESC)

分页:由于ROWNUM不支持>=,那么可以采用在子查询外边加一层查询子查询的ROWNUM的语句,使子查询的ROWNUM(取个别名)结果作为外层查询的条件使用。这样就可以使用>=了。

SELECT E.R,E.ENAME,E.SAL FROM
(SELECT ROWNUM R,ENAME,SAL FROM(SELECT ROWNUM,ENAME,EMPNO,SAL FROM EMP ORDER BY SAL DESC) WHERE ROWNUM <=3)E
WHERE E.R>=2

练习：

练习一.查询出员工表中工资最高的前三名，格式如下：

SELECT ROWNUM R,ENAME,SAL FROM(SELECT ROWNUM,ENAME,EMPNO,SAL FROM EMP ORDER BY SAL DESC) WHERE ROWNUM <=3

练习二.查询出员工表中薪水大于本部门平均薪水的员工，结果如下:

方法一:

SELECT E.EMPNO,E.ENAME,E.SAL,D.S
FROM EMP E, (SELECT DEPTNO,AVG(SAL) S FROM EMP GROUP BY DEPTNO)D
WHERE E.DEPTNO=D.DEPTNO AND E.SAL>D.S

方法二:

相关子查询:将主查询中的某个值 作为参数传递给子查询

SELECT EMPNO,ENAME,SAL,(SELECT AVG(SAL) FROM EMP WHERE DEPTNO=E.DEPTNO)
FROM EMP E
WHERE SAL>(SELECT AVG(SAL) FROM EMP WHERE DEPTNO=E.DEPTNO)--子查询根据主查询的员工DEPTNO,查询出员工所在部门的工资平均值

练习三

create table pm_ci
(ci_id varchar(20) primary key,
stu_ids varchar(100));

insert into pm_ci values('1','1,2,3,4');
insert into pm_ci values('2','1,4');

create table pm_stu
(stu_id varchar(20) primary key,
stu_name varchar(20));
insert into pm_stu values('1','张三');
insert into pm_stu values('2','李四');
insert into pm_stu values('3','王五');
insert into pm_stu values('4','赵六');

这道题需要用到WM_CONCAT函数:  按照部门分组,将同一部门的员工姓名显示在一行中

要用这个函数,首先需要把表的查询结果变成下面这种形式

部门    员工姓名

1           xxc1

2           xxc2

3           xxc3

所以需要把第三题的两张表变成这种形式

课程编号     学生姓名

1                    张三

1                    李四

2                    张三

           ......

由于PM_CO表中STU_IDS是字符串类型,那么就可以用到INSTR(a,b)函数当a中包含b就返回b的索引位置(从1开始),不包含返回0.

SELECT C.CI_ID,S.STU_NAME
FROM PM_STU S,PM_CI C
WHERE INSTR(C.STU_IDS,S.STU_ID)>0

查询结果如下：

然后就可以使用WM_CONCAT函数:

SELECT CI_ID,WM_CONCAT(STU_NAME) NAMES FROM
(SELECT C.CI_ID,S.STU_NAME
FROM PM_STU S,PM_CI C
WHERE INSTR(C.STU_IDS,S.STU_ID)>0)
GROUP BY CI_ID