中每条
记录的行号,ROWNUM字段的值是在查询过程中动态生成的,只要从列表中查数据,rownum就会
从1开始进行记录行号
SELECT ROWNUM,ENAME,JOB,SAL FROM EMP_GH WHERE ROWNUM>=1 AND ROWNUM<=10
在使用rownum中对结果集进行编号过程中,不能通过rownum大于1及以上数字判断,否则
查不出任何结果
SELECT *
FROM(SELECT ROWNUM RN,ENAME,SAL FROM EMP_GH) WHERE RN between 5 and 10
** 工资排名第六到第10位 嵌套2层子查询 SELECT *--有行号的浏览表 FROM
(SELECT ROWNUM R,SAL--对表加行号 FROM
(SELECT SAL FROM EMP_GH ORDER BY SAL DESC))--排序 WHERE R BETWEEN 6 AND 10 --增加浏览过滤条件
若在分页中有序排列需求,那么先应该排序,因为排序优先级最低
***分页经典 优化
SELECT *
FROM (SELECT ROWNUM RN,T.*
FROM ( SELECT ENAME,SAL,DEPTNO FROM EMP_GH ORDER BY SAL DESC) T WHERE ROWNUM<=10) WHERE RN>=6
***pageSize:每页显示的条目数 page:第几页
根据上述两个参数,计算结果的集范围: START:(page-1)*pageSize+1 END:pageSize*page
****DECODE函数,处理分支业务 给不同职位的人员涨工资: MANAGER:20% ANALYST:10% SALESMAN:5%
DECODE(JOB, 'MANAGER',SAL*1.2)当第一个参数等于第二个参数,则返回第三个参数 SELECT ENAME,JOB,SAL,
DECODE(JOB, 'MANAGER',SAL*1.2, 'ANALYST',SAL*1.1, 'SALESMAN' ,SAL*1.05, SAL ) BOUNS FROM EMP_GH CASE 语句
SELECT ENAME,SAL,
CASE JOB WHEN 'MANAGER' THEN SAL*1.2 WHEN 'ANALYST' THEN SAL*1.1 WHEN 'SALESMAN' THEN SAL*1.05 ELSE SAL END BOUNDS FROM EMP_GH 可以指定范围.
CASE WHEN JOB >0 THEN SAL*1.2
WHEN JOB >0 THEN SAL*1.1 WHEN JOB >0 THEN SAL*1.05 ELSE sal END BOUNDS
******查看MANAGER,ANALYST部门的总人数和其他部门的总人数 SELECT count(*),DECODE(JOB,'MANAGER','VIP', 'ANALYST','VIP', 'OTHER') BB FROM EMP_GH
GROUP BY DECODE(JOB,'MANAGER','VIP',
'ANALYST','VIP', 'OTHER')
**排序 SELECT DEPTNO ,DNAME,LOC FROM DEPT_GH
ORDER BY DECODE(DNAME,'OPERATIONS',1,'ACCOUNTING',2,'SALES',3)
*****排序函数:
可以根据结果集,按照指定的字段分组,在组内进行排序,生成组内编号 ****ROW_NUMBER:**************连续,唯一的 ROW_NUMBER() OVER(
PARTITION BY DEPTNO--按照部门号分组 ORDER BY SAL DESC--按照工资进行排名
)
查看每个部门工资的排名
SELECT ENAME,SAL,ROW_NUMBER() OVER( PARTITION BY DEPTNO ORDER BY SAL DESC) BB FROM EMP_GH
*****RANK() OVER(---生成不连续,不唯一的数,会出现并列排名情况 PARTITION BY deptno ORDER BY sal DESC )
SELECT ENAME,SAL,RANK() OVER(PARTITION BY DEPTNO ORDER BY SAL DESC) FROM EMP_GH
******DENSE_RANK() OVER()--组内生成连续的不唯一的编号,就是出现并列后,不跳序号 SELECT ENAME,SAL,DENSE_RANK() OVER(PARTITION BY DEPTNO ORDER BY SAL DESC) BB
FROM EMP_GH ******
CREATE TABLE SALES_GH( YEAR_ID NUMBER NOT NULL, MONTH_ID NUMBER NOT NULL, DAY_ID NUMBER NOT NULL,
SALES_VALUE NUMBER(10,2) NOT NULL );
INSERT INTO SALES_GH
SELECT TRUNC(DBMS_RANDOM.VALUE(2010,2012)) AS YEAR_ID, TRUNC(DBMS_RANDOM.VALUE(1,13)) AS MONTH_ID, TRUNC(DBMS_RANDOM.VALUE(1,32)) AS DAY_ID,
ROUND(DBMS_RANDOM.VALUE(1,100),2) AS SALES_VALUE FROM DUAL
CONNECT BY LEVEL<=1000;
DESC SALES_GH
SELECT * FROM SALES_GH
******集合操作
集合操作的字段顺序,结果必须一样 ******并集,UNION ,全并UNION ALL SELECT ENAME,SAL,JOB
FROM EMP_GH
WHERE JOB='MANAGER' UNION
SELECT ENAME,SAL,JOB FROM EMP_GH WHERE SAL>2000
SELECT ENAME,SAL,JOB FROM EMP_GH
WHERE JOB='MANAGER' UNION ALL
SELECT ENAME,SAL,JOB FROM EMP_GH WHERE SAL>2000
*********交集
INTERSECT:共有的
SELECT ENAME,SAL,JOB FROM EMP_GH
WHERE JOB='MANAGER' INTERSECT
SELECT ENAME,SAL,JOB FROM EMP_GH WHERE SAL>2000 *******差集
MINUS 一边有,其他没有的,
就是第一个查询语句减去第二个查询语句中的 SELECT ENAME,SAL,JOB FROM EMP_GH WHERE SAL>3000 MINUS
SELECT ENAME,SAL,JOB FROM emp_gh
WHERE JOB='MANAGER'
**************高级分组函数
*****ROLLUP(A,B,C...n) 参数在逐个递减可以使用rollup进行合并,实现了n+1次分组 SELECT YEAR_ID,MONTH_ID,DAY_ID,SUM(SALES_value) FROM sales_gh
GROUP BY ROLLUP(YEAR_ID,MONTH_ID,DAY_ID) ORDER BY YEAR_ID,MONTH_ID,DAY_ID
**** CUBE (A,B,C,...N) 每个参数都进行匹配,2^n个组合 SELECT YEAR_ID,MONTH_ID,DAY_ID,SUM(SALES_VALUE) FROM SALES_GH
GROUP BY CUBE(YEAR_ID,MONTH_ID,DAY_ID) ORDER BY YEAR_ID,MONTH_ID,DAY_ID
***GROUPING SETS(A,B,C,..)按照指定的分组方法进行分组 SELECT YEAR_ID,MONTH_ID,DAY_ID,SUM(SALES_VALUE) FROM SALES_GH
GROUP BY GROUPING SETS((YEAR_ID,MONTH_ID,DAY_ID),(YEAR_ID,MONTH_ID)) ORDER BY YEAR_ID,MONTH_ID,DAY_ID
***********test**************************** 1:查看与CLARK相同职位的员工 SELECT ENAME FROM EMP_GH
WHERE JOB=(SELECT JOB FROM EMP_GH WHERE ENAME='CLARK') 2:查看低于公司平均工资的员工 SELECT ENAME,SAL FROM EMP_GH
WHERE SAL<(SELECT AVG(SAL) FROM EMP_GH ) 3:查看与ALLEN同部门的员工 SELECT ENAME,DEPTNO FROM EMP_GH
WHERE DEPTNO=(SELECT DEPTNO FROM EMP_GH WHERE ENAME='ALLEN') 4:查看平均工资低于20号部门平均工资的部门平均工资 SELECT DEPTNO,AVG(SAL) FROM EMP_GH GROUP BY DEPTNO
HAVING AVG(SAL)<(SELECT AVG(SAL) FROM EMP_gh GROUP BY DEPTNO HAVING DEPTNO=20) 5:查看低于自己所在部门平均工资的员工 SELECT ENAME
FROM EMP_GH E,(SELECT AVG(SAL) S,DEPTNO FROM EMP_GH GROUP BY DEPTNO) D WHERE E.DEPTNO=D.DEPTNO AND E.SAL 6:查看公司工资排名的第1-5名 SELECT * FROM (SELECT ROWNUM,T.* FROM ( SELECT ENAME,SAL FROM EMP_GH ORDER BY SAL DESC) T) WHERE ROWNUM BETWEEN 1 AND 5 7:查看CLERK职位的人数和其他职位的总人数各多少? SELECT COUNT(*),DECODE(JOB,'CLERK','CLERK', 'OTHER') M FROM EMP_GH GROUP BY DECODE(JOB,'CLERK','CLERK', 'OTHER')