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