oracle内置函数

1.Oracle 分析函数——CUBE ， ROLLUP

功能描述：

如果是 ROLLUP(A, B, C) 的话， GROUP BY 顺序

(A 、 B 、 C)

(A 、 B)

(A)

最后对全表进行 GROUP BY 操作。

如果是 GROUP BY CUBE(A, B, C) ， GROUP BY 顺序

(A 、 B 、 C)

(A 、 B)

(A 、 C)

(A) ，

(B 、 C)

(B)

(C) ，

最后对全表进行 GROUP BY 操作。

2.分析函数——GROUPING

GROUPING函数的作用是放总记

如一个公司有多个部门，一个部门有多个岗位，一个岗位上有多个人
Rollup(部门,工作岗位)  sum(每人的工资)
当部门的GROUPING为0，工作岗位的GROUPING也为0时，说明是公司发的总工资，此时放公司总计
当部门的GROUPING为0，工作岗位的GROUPING也为1时，说明是部门发的总工资，此时放部门小记
当部门的GROUPING为1，工作岗位的GROUPING也为1时，显示的是某部门某职位的工资和计
SQL> SELECT   department_id DEPTID, job_id JOB,
  2           SUM(salary),
  3           GROUPING(department_id) GRP_DEPT,
  4           GROUPING(job_id) GRP_JOB
  5  FROM     employees
  6  GROUP BY ROLLUP(department_id, job_id);

3.GROUPING SETS与GROUPING的作用是不同的

Oracle服务器计算GROUPING SETS子句中所有的组并将结果通过UNION ALL组合成一个结果集.
GROUPING SETS的效果:1.只需要访问一次基表.2.不需要写很复杂的UNION语句.
SELECT   department_id, job_id, null manager_id,avg(salary)
FROM     employees
GROUP BY (department_id,job_id)
UNION ALL
SELECT   null department_id, job_id, manager_id,avg(salary)
FROM     employees
GROUP BY (job_id,manager_id)
等同于
SQL> set autotrace on
SQL> SELECT   department_id, job_id,
  2           manager_id,avg(salary)
  3  FROM     employees
  4  GROUP BY GROUPING SETS ((department_id,job_id), (job_id,manager_id));

4.Rank() 于Dense_rank()

rank(),连续排名

dense_rank(),非连续排名

转载于：http://blog.csdn.net/cczz_11/article/details/6053539

·查询各学生科目为Oracle排名(简单排名)

select sc.s_id,sc.s_name,sub_name,sc.score,

 rank() over (order by score desc) 名次

from t_score sc

where sub_name='Oracle'

·查询各学生各科排名(分区排名，分区非连续排名)

select sc.s_id,sc.s_name,sub_name,sc.score,

 rank() over

 (partition by sub_name order by score desc) 名次

from t_score sc

·查询各学生科目为Oracle排名(简单排名)

select sc.s_id,sc.s_name,sub_name,sc.score,

 rank() over (order by score desc) 名次

from t_score sc

where sub_name='Oracle'

·查询各科前2名(分区排名)

·类似：新闻表,求栏目点击率在前3位的新闻。

商品表,求各类别销售额在前10位的商品。

select * from (

select sc.s_id,sc.s_name,sub_name,sc.score,

dense_rank() over

(partition by sub_name order by score desc) 名次

from t_score sc

) x

where x.名次<=2

 

·查询各学生科目为Oracle排名(简单排名)

select sc.s_id,sc.s_name,sub_name,sc.score,

 rank() over (order by score desc) 名次

from t_score sc

where sub_name='Oracle'