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 case.when group by

数据库 t 表 b 表内容 Id Name 胜负 1 张三 胜 2 李四 ...

8、Oracle:group by用法

本文来自: http://blog.csdn.net/basenet855x/article/details/6694150 oracle 中GROUP BY的用法 问题: select ...

Oracle分组ROLLUP、GROUP BY、GROUPING、GROUPING SETS区别和作用

1.ROLLUPROLLUP的作用相当于SQL> set autotrace on SQL> select department_id,job_id,count(*) from employe...

oracle group by 中rollup和cube

Oracle的GROUP BY语句除了最基本的语法外,还支持ROLLUP和CUBE语句。如果是ROLLUP(A, B, C)的话,首先会对(A、B、C)进行GROUP BY,然后对(A、B)进行GRO...
  • quanhj
  • quanhj
  • 2011-02-09 13:35
  • 2232

Oracle中group by用法

Oracle中group by用法 在select 语句中可以使用group by 子句将行划分成较小的组,一旦使用分组后select操作的对象变为各个分组后的数据,使用聚组函数返回的是每一个组...

oracle中distinct和group by的区别

其实二者没有什么可比性,但是对于不包含聚集函数的GROUP BY操作来说,和DISTINCT操作是等价的。不过虽然二者的结果是一样的,但是二者的执行计划并不相同。 在Oracle9i中:...

Oracle中group by 的使用

在select 语句中可以使用group by 子句将行划分成较小的组,然后,使用聚组函数返回每一个组的汇总信息,另外,可以使用having子句 限制返回的结果集。group by 子句可以将查询结果...
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:深度学习:神经网络中的前向传播和反向传播算法推导
举报原因:
原因补充:

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