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查询报错: 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 #...
  • u014520745
  • u014520745
  • 2017年07月25日 10:34
  • 9155

SQL---Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column '

数据库中插入数据或执行sql语句时一直报下面这个错误: Expression #1 of ORDER BY clause is not in GROUP BY clause and contains ...
  • weixin_39800144
  • weixin_39800144
  • 2017年12月27日 17:45
  • 286

ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregate

ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregate...
  • qq285744011
  • qq285744011
  • 2017年06月12日 10:02
  • 3299

[Err] 1055 - Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated..

[Err] 1055 - Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated c...
  • zq33312757
  • zq33312757
  • 2016年11月10日 18:08
  • 1802

[Err] 1055 - Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated c

mysql  5.7.13 下出现的问题查询异常 在 /etc/my.cnf 文件里加上如下: sql_mode='NO_ENGINE_SUBSTITUTION' 然后,重启Mysql服...
  • u010320371
  • u010320371
  • 2016年06月19日 10:08
  • 13346

Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column 'inform

Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column 'inform...
  • u012129607
  • u012129607
  • 2017年03月20日 13:18
  • 4111

mysql遇见Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggre的问题

报错如下: Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column ...
  • qq_34707744
  • qq_34707744
  • 2017年09月19日 16:20
  • 4788

MySQL: Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'sss

执行MySQL中sql语句的时候报错,执行的sql语句:SELECT prov_desc, area_desc, month_id, MAX(total_fee)AS ...
  • u010429286
  • u010429286
  • 2017年03月21日 16:55
  • 6221

ERROR 1055 (42000): Expression #1 of ORDER BY clause is not in GROUP BY..sql_mode=only_full_group_by

MySQL错误:Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column...
  • qq285744011
  • qq285744011
  • 2017年10月19日 19:02
  • 594

mysql报错及其解决方式集合

1、在使用group by 查询一张表的数据的时候:select date,time,max(delaytime) as delaytime,sum(delaynum) as delaynum, ma...
  • v587_lu
  • v587_lu
  • 2016年06月30日 09:27
  • 13942
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:Oracle -- Extended Group By Clause
举报原因:
原因补充:

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