Oracle -- Extended Group By Clause

原创 2012年03月24日 12:48:00

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.

Mysql 升级到 5.7.16,group by 一直报home brew on columns in GROUP BY clause; this is incompatible with sql

Mysql 版本➜ /etc mysql -v Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connec...

mysql查询报错: ORDER BY clause is not in GROUP BY..this is incompatible with sql_mode=only_full_group_by

在用mysql执行如下查询的时候: select * from `sys_user_group` group by `GROUP_ID`报错信息如下:[Err] 1055 - Expression #...

Mysql [Err] 1055 - Expression #1 of ORDER BY clause is not in GROUP BY clause

select version(); # 5.7.17常规查询报错: [Err] 1055 - Expression #1 of ORDER BY clause is not in GROUP BY ...

oracle数据库的sql平时遇到的一些小问题union,to_char(),null last,group by

1.在oracle中as只能对字段起别名,不能对表起别名,表的别名只能用空格(字段 as 别名/ 表名   表别名) 2.如果使用group by 则group by 后的字段除了函数以外,别的都要...

Oracle pl/sql中的group by子句不能使用别名,应该这样写。

下面的语句执行的话会报错:ORA-00904: "CALLT": 标示符无效 select case when ta.call_time = 0 then 0 when ta.call_tim...

【oracle】数据库简单操作——group by

我有一个students表,有id,name,gender(性别)三个字段;其中有5个学生; 一、查询students表; 1、通过desc students命令查询表结构如下: 2、通...

oracle中的having ,group by ,where之间联系与区别

having子句与where有相似之处但也有区别,都是设定条件的语句。 在查询过程中聚合语句(sum,min,max,avg,count)要比having子句优先执行.而where子句在查询过程中执...

Oracle高级查询之GROUP BY

http://blog.csdn.net/ayou2008/article/details/7183258 为了方便大家学习和测试,所有的例子都是在Oracle自带用户Scott下建...

Oracle group by及其若干相关函数的一些说明

Oracle的group by除了基本用法以外,还有3种扩展用法,分别是rollup、cube、grouping sets。 假设有一个表test,有A、B、C、D、E5列。 1) 如...

oracle case.when group by

数据库 t 表 b 表内容 Id Name 胜负 1 张三 胜 2 李四 ...
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:Oracle -- Extended Group By Clause
举报原因:
原因补充:

(最多只允许输入30个字)