关闭

Oracle -- Extended Group By Clause

标签: oraclefilternull
225人阅读 评论(0) 收藏 举报
分类:

Oracle -- Extended Group By Clause

 

ROLLUP(), CUBE(), GROUPING_SETS(), GROUPING(), GRUOPING_ID(), GROUP_ID()

 

1. ROLLUP()

--返回n+1层小计

a)

select division_id, job_id, sum(salary)
from EMPLOYEES2
group by division_id, job_id
order by division_id, job_id

b)

select division_id, job_id, sum(salary)
from EMPLOYEES2
group by rollup(division_id, job_id)
order by division_id, job_id

--use CASE + GROUPING() to convert multiple column values.

c)

select division_id, job_id, sum(salary)
from EMPLOYEES2
group by division_id, rollup(division_id, job_id)
order by division_id, job_id

--Compared with option b. Duplicate records returned by group by division_id, The grand total of all groups is not returned.

--use GROUP_ID() to filter out duplicate records.

d)

select division_id, job_id, sum(salary)
from EMPLOYEES2
group by division_id, rollup(job_id)
order by division_id, job_id
--Compared with option b. No duplicated records returned.

--Except the grand total of all groups is not returned, the rest result are as same as option b.

 

 2, CUBE()

--返回2的N次方小计

 

3, GROUPIN()

--Return 1 if the column value is NULL, otherwise returns 0.

 

4, GROUP_ID()

--Return 0 to n-1 if n duplicate records exist.

0
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:18723次
    • 积分:405
    • 等级:
    • 排名:千里之外
    • 原创:24篇
    • 转载:3篇
    • 译文:0篇
    • 评论:0条