SQL语句——15、高级分组

ALiDan 2020-01-04

rollup 和 cube 

group by 生成数据结果时,只会生成相关列的数据统计信息,而不是生成小计和总计

group by统计结果如下（不是select直接返回格式）:

SQL> select deptno,job,avg(sal) from emp group by deptno,job;
--------------------------------------------------------------
|deptno\job| clerk | analyst | manager | president | salesman |--------------------------------------------------------------
| 10      | 1300 |          | 2450    | 5000      |         |
--------------------------------------------------------------
| 20      | 950  |   3000   | 2975    |           |         |
--------------------------------------------------------------
| 30      | 950  |          | 2850    |           | 1400    |
--------------------------------------------------------------

rollup操作在保留group by统计结果的基础上,还会生成横向小计(部门平均工资)和总计(所有员工的平均工资)

select deptno,job,avg(sal) from emp group by rollup(deptno,job);
-----------------------------------------------------------------------------
|deptno\job | clerk | analyst | manager | president | salesman | 小计        |-----------------------------------------------------------------------------
| 10        | 1300  |         | 2450    | 5000      |          | 2916.66667 |
-----------------------------------------------------------------------------
| 20        | 950   | 3000    | 2975    |           |          | 2175       |
-----------------------------------------------------------------------------
| 30        | 950   |         | 2850    |           | 1400     | 1566.66667 |
-----------------------------------------------------------------------------
| 合计       |       |         |         |           |          | 2073.21429 |
-----------------------------------------------------------------------------

cube 是在group by rollup 的基础上,再做出纵向小计:

SQL> select deptno,job,avg(sal) from emp group by cube(deptno,job);
-----------------------------------------------------------------------------
|deptno\job | clark | analyst | manager | president | salesman | 小计        |-----------------------------------------------------------------------------
| 10        | 1300  || 2450    | 5000      |          | 2916.66667 |
-----------------------------------------------------------------------------
| 20        | 950   | 3000    | 2975    |           |          | 2175       |
-----------------------------------------------------------------------------
| 30        | 950   |         | 2850    |           | 1400     | 1566.66667 |
-----------------------------------------------------------------------------
| 职位小计   |1037.5 | 3000     |2758.333 | 5000     | 1400      |            |
-----------------------------------------------------------------------------
| 合计      |       |          |         |           |          | 2073.21429 |
-----------------------------------------------------------------------------

ROLLUP sql写法:

SQL> select deptno,job,avg(sal) from emp group by rollup (deptno,job);

DEPTNO        JOB     AVG(SAL)
---------- --------- ----------
10          CLERK      1300
10          MANAGER    2450
10          PRESIDENT  5000
10                     2916.66667
20          CLERK      950
20          ANALYST    3000
20          MANAGER    2975
20                     2175
30          CLERK      950
30          MANAGER    2850
30          SALESMAN   1400
30                     1566.66667
                       2073.21429

13 rows selected.

CUBE sql写法：

SQL> select deptno,job,avg(sal) from emp group by cube (deptno,job) order by 1;

DEPTNO         JOB     AVG(SAL)
---------- --------- ----------
10           CLERK      1300
10           MANAGER    2450
10           PRESIDENT  5000
10                      2916.66667
20           ANALYST    3000
20           CLERK      950
20           MANAGER    2975
20                      2175
30           CLERK      950
30           MANAGER    2850
30           SALESMAN   1400
30                      1566.66667
             ANALYST    3000
             CLERK      1037.5
             MANAGER    2758.33333
             PRESIDENT  5000
             SALESMAN   1400
                        2073.21429

18 rows selected.

解析rollup语句

rollup(a,b,c)

等同于

group by a,b,c
union all
group by a,b
union all
group by a
union all
group by null

为维持输出列的数量一直 select子句中用null来代替

举例：

SQL> select deptno,job,sum(sal) from emp group by rollup(deptno,job);

DEPTNO        JOB     SUM(SAL)
---------- --------- ----------
          CLERK     1300
          MANAGER   2450
          PRESIDENT 5000
                    8750
          CLERK     1900
          ANALYST   6000
          MANAGER   2975
                    10875
          CLERK     950
          MANAGER   2850
          SALESMAN  5600
                    9400

rows selected.

-----分解为:
SQL> select deptno,job,sum(sal) from emp group by deptno,job
union all
select deptno,null,sum(sal) from emp group by deptno
union all
select null,null,sum(sal) from emp
order by 1;

DEPTNO        JOB     SUM(SAL)
---------- --------- ----------
           CLERK     1300
                     8750
           MANAGER   2450
           PRESIDENT 5000
           ANALYST   6000
           MANAGER   2975
           CLERK     1900 
                     10875
           SALESMAN  5600
           CLERK     950
           MANAGER   2850
                     9400

rows selected.

解析cube语句

cube(a,b,c) 分解成2的N次方个union all组合

等同于:

group by a,b,c
union all
group by a,b
union all
group by a
union all
group by a,c
union all
group by b,c
union all
group by b
union all
group by c
union all
group by null

举例：

SQL> select deptno,job,sum(sal) from emp group by cube(deptno,job);

--------解析为:
SQL> select deptno,job,sum(sal) from emp group by deptno,job
union all
select deptno,null,sum(sal) from emp group by deptno
union all
select null,job,sum(sal) from emp group by job
union all
select null,null,sum(sal) from emp
order by 1

grouping

在使用rollup和cube时统计结果可能用到一列也可能用到多列,为了确定统计结果是否使用了特定列才使用grouping

返回为0 代表使用了该列

返回为1 代表没使用该列

SQL> select deptno,job,avg(sal),grouping(deptno),grouping(job) from emp
group by cube (deptno,job) order by 1;

DEPTNO        JOB       AVG(SAL) GROUPING(DEPTNO) GROUPING(JOB)
---------- --------- ---------- ---------------- -------------
10           CLERK       1300         0                0
10           MANAGER     2450         0                0
10           PRESIDENT   5000         0                0
10                       2916.66667   0                1
20           ANALYST     3000         0                0
20           CLERK       950          0                0
20           MANAGER     2975         0                0
20                       2175         0                1
30           CLERK       950          0                0
30           MANAGER     2850         0                0
30           SALESMAN    1400         0                0
30                       1566.66667   0                1
             ANALYST     3000         1                0
             CLERK       1037.5       1                0
             MANAGER     2758.33333   1                0
             PRESIDENT   5000         1                0
             SALESMAN    1400         1                0
                         2073.21429   1                1

18 rows selected.

grouping sets

GROUPING SETS是一个GROUP BY的增强语句，能让你随心所欲的指定多个分组列小计或合计，ORACLE会用UNION ALL将GROUPING SETS中制定的每组合计组合在一起。

GROUPING SETS相对与多个GROUP BY的优势：

  1. 只要一次扫描基表，能得到更好的性能
  2. 不需要写复杂的UNION语句
select deptno,job,avg(sal) from emp group by grouping sets(deptno,job);
--------实际就是两个group by的union all--------
select deptno,null,avg(sal) from emp group by deptno
union all
select null,job,avg(sal) from emp group by job;
ALiDan

ALiDan

相关推荐

对两个字段分组

在平时的开发任务中我们经常会用到MYSQL的GROUP BY分组， 用来获取数据表中以分组字段为依据的统计数据。比如有一个学生选课表，表结构如下：。GROUP BY X意思是将所有具有相同X字段值的记录放到一个分组里。从表中的记录我们可以看出这个分组结果是

godfather / 0评论 2020-06-26

报表怎么做“其他”分组

SQL 中分组是很常见的运算，是将一个集合按照某种规则拆分成若干个子集，常常伴随着对子集的进一步汇总计算。报表中我们通常称这种分组为“等值分组”。实现这种等值分组也很容易，只需要按照指定字段做 group(), 再针对分组做聚合运算即可。有时除了上述等值分

ItBJLan / 0评论 2020-06-17

分组汇总 | 分组汇总去重_了解几种汇总的用法

select fCompany,fFeeStatus,sum as [某家公司的总数 ] --如果fFeeStatus状态只有一种时可以这样做，如果有多种就不可这样做了。from tbGroupSum0605

dreamhua / 0评论 2020-06-05

sql连接查询和分组查询

FROM <表名> WHERE ……SELECT COUNT(*) AS 人数, SGrade AS 年级 FROM Students GROUP BY SGrade. SELECT COUNT(*)........FROM表名 COUNT B

明月清风精进不止 / 0评论 2020-04-21

SQL分组查询GroupBy加having

“Group By”从字面意义上理解就是根据“By”指定的规则对数据进行分组，所谓的分组就是将一个“数据集”划分成若干个“小区域”，然后针对若干个“小区域”进行数据处理。where 子句的作用是在对查询结果进行分组前，将不符合where条件的行去掉，即在分

明月清风精进不止 / 0评论 2020-02-17

MySQL之指定过滤条件（HAVING）

在 MySQL SELECT 语句中，除了能使用 GROUP BY 子句分组数据外，还可以使用 HAVING 子句过滤分组，在结果集中规定了包含哪些分组和排除哪些分组。其中，<条件>指的是指定的过滤条件。HAVING 子句和 WHERE 子句非

ALiDan / 0评论 2020-01-12

MySQL之分组查询（GROUP BY）

在 MySQL SELECT 语句中，允许使用 GROUP BY 子句，将结果集中的数据行根据选择列的值进行逻辑分组，以便能汇总表内容的子集，实现对每个组而不是对整个结果集进行整合。可以指定多个列，彼此间用逗号分隔。GROUP BY 子句可以包含任意数目的

ztyzly00 / 0评论 2020-01-12

SQL语句——08、聚集,分组,行转列

count 为列值时空不在统计之内，为*时包含空行和重复行。上面执行的聚集函数都是对所有记录统计，如果想分组统计需要使用group by，为了限制分组统计的结果需要使用having过滤。idle> select deptno,avg from emp

tanrong / 0评论 2019-12-28

SQL语句的补充(一)

select 分组列,聚合函数 from 表名 group by 分组列。--如果分组的列有多个，则要多个列的条件完全一致才会被化为一组。-- having 后面的条件需要注意条件的参数。-- 前三种是常用的。select 列名, 别名 from 表名。如

dreamhua / 0评论 2019-12-26

分享一则性能优化案例--分组汇总的妙用

概述今天主要分享一个mysql数据库性能优化的案例，这里通过一个需求实现来体验下两个方案的优劣。需求在公司门户系统中实现每个用户查看各自发布的文章列表的时候，能够在文章名称后面显示该文章的留言数量。这里大家可以想一下自己会怎么去实现..总结从整体资源消耗来

81911536 / 0评论 2019-12-14

mysql多种条件分组排序求某类最新一条

主要是对group by 理解的不够深入。才出现这样的情况，后来网上学习了一下，并记录下来分享给大家。select * from test group by category_id order by `date`. select a.* from test

xiaobaif / 0评论 2019-12-13

sql中筛选第一条记录【分组排序】

我们现在有一张表titles，共有4个字段，分别是emp_no，title(职位)，from_date，to_date，记录的是员工在某个时间段内职位名称，因为会存在升职，转岗之类的，里面emp_no可能会对应多个职位，我们现在要取到所有员工最近的职位信息

一对儿程序猿 / 0评论 2019-12-08

Mysql常用查询

IN/NOT IN操作符：查询满足（不满足）指定范围内的条件的记录。MYSQL> SELECT s_id, s_name FROM fruits WHERE s_id IN ;BETWEEN AND/NOT BETWEEN AND查询： 查询指定范围

azhou / 0评论 2019-10-22

Oracle分组函数之ROLLUP的基本用法

本博客简单介绍一下oracle分组函数之rollup的用法，rollup函数常用于分组统计，也是属于oracle分析函数的一种。create table dept as select * from scott.dept;create table emp a

oraclemch / 0评论 2019-07-05

MySQL实现按天分组统计，提供完整日期列表，无数据自动补0

业务需求最近要在系统中加个统计功能，要求是按指定日期范围里按天分组统计数据量，并且要能够查看该时间段内每天的数据量。解决思路直接按数据表日期字段group by统计，发现如果某天没数据，该日期是不出现的，这不太符合业务需求。参考Oracle的“select

sqlitexin / 0评论 2019-07-25

分组查询GROUP BY的使用与SQL执行顺序的讲解

在SQL中使用GROUP BY来对SELECT的结果进行数据分组，在具体使用GROUP BY之前需要知道一些重要的规定。也就是说可以在组里再分组，为数据分组提供更细致的控制。

汉武帝刘彻 / 0评论 2019-03-19

SQL 难点解决：直观分组

示例 2：按顺序分别列出使用 Chinese、English、French 及其它语言作为官方语言的国家数量。with t as (select 'Chinese',1 union all select 'English',2. union all sel

YFCEMBEDD / 0评论 2019-06-30

hive row_number分组排序top

自从hive 0.11.0 开始，加入了类似orcle的分析函数，很强大，可以查询到分组排序top值。一：语法(用法)： rank() over dense_rank() over row_number() over 其中[partition by col

zjyzz / 0评论 2014-12-08

ORA-00937: 不是单组分组函数

今天在java中拼接sql时犯了一个很低级的错误，sql语句拼接好以后，查询总是报ORA-00937: 不是单组分组函数，检查了好久都没没有找到错误的原因，在控制台打印的sql语句放到pl/SQL中执行也没错，但死活在junit测试的时候报ORA-0093

dropkai / 0评论 2011-06-10

写一个“特殊”的查询构造器 - (五、聚合函数、分组、排序、分页)

where 相关的子句构造完成后，我们继续构造其它子句。这一篇我们进行聚合函数、分组、排序等子句的构造。聚合函数在 SQL 中，有一些用来统计、汇总的函数，被称作聚合函数，如 SUM、COUNT、AVG 等。使用 select() 方法时，我们可以用 se

Depth / 0评论 2019-06-27

group by 两个字段 【给我发表点意见】

列出一组数据，然后再依第二个[STATINEVENTID]分组。要是真想加一个字段并不在聚合函数内，还没有解决方法！

qshpeng / 0评论 2011-03-17

计算分组后的记录行数（count group）

小弟有这样一个问题：想用hql计算分组之后的总记录行数？这时语法都通不过啊！！小弟我相了好多方法，都不行啊，大哥们，难就只能用sql了吗？

gwn00 / 0评论 2006-11-04

sql语句增删改查及模糊查询，排序分组的查询

这就相当于给tsuiau设置了别名student那么下面我们查询tsuiau表的时候可以直接使用student. 分页查询limitselect * from emp limit 0， 5 从第一行开始查，一共查5行。假如规定每页有10行记录，那么如果查询

li00lee / 0评论 2018-04-15

Oracle增强型分组函数

ROLLUP()函数是对于GROUP BY 分组统计的功能扩展，可以实现分组统计求和的效果。SQL>CREATE TABLE employee_salary ASSELECT E.FIRST_NAME,E.JOB_ID,E.MANAGER_ID,E.

mouseshuai / 0评论 2014-11-20

Oracle分组取第一条数据

SELECT ROW_NUMBER() OVER(PARTITION BY PK_FINANCE_ACCOUNT ORDER BY amassdate DESC) LEV,count(0) over(partition by PK_FINANCE_ACCO

sqlitexin / 0评论 2012-05-26

Oracle 分组查询详解

Avg ，count，max，min，stddev，sum。如果在查询的过程中需要按某一列的值进行分组,以统计该组内数据的信息时,就要使用group by子句。SQL> select avg from emp group by deptno;SQL&

oracleKing / 0评论 2012-03-14

详解MySQL分组链接的使用技巧

MYSQL中的分组和链接是在操作数据库和数据交互时最常用的两个在功能，把这两项处理好了，MYSQL的执行效率会非常高速。顾名思义，把数据按什么来分组，每一组都有什么特点。查询所有数据的条数，按性别来分组。这样查询到的结果集只有一列count(*)。SELE

menshen / 0评论 2019-04-16

MySql Group By对多个字段进行分组的实现方法

在平时的开发任务中我们经常会用到MYSQL的GROUP BY分组， 用来获取数据表中以分组字段为依据的统计数据。比如有一个学生选课表，表结构如下：。MKB114 2因为表里记录了有5个学生选择ITB001，2个学生选择了MKB114。还有两个学生在第一

DavinTanger / 0评论 2019-04-16

深入浅析SQL中的group by 和 having 用法

Group By语句从英文的字面意义上理解就是“根据一定的规则进行分组”。having称为分组过滤条件，也就是分组需要的条件，所以必须与group by联用。having子句限制的是组，而不是行。

李轮清 / 0评论 2019-04-09

sql中的 where 、group by 和 having 用法解析

--如果要用到group by 一般用到的就是“每这个字” 例如说明现在有一个这样的表：每个部门有多少人 就要用到分组的技术。select DepartmentID as '部门名称',COUNT(*) as '个数' from BasicDepartme

yushuir / 0评论 2019-04-09

分组后分组合计以及总计SQL语句（稍微整理了一下）

SELECT 分组字段 FROM 表GROUP BY 分组字段compute sum. FROM 别名4）统计分组后的种类数：例子1：分组合计。SELECT JSSKQK_JGH FROM SJ_JSSKQK WHERE JSSKQK_JGH IN GR

cjylean / 0评论 2019-04-09

sql分组后二次汇总(处理表重复记录查询和删除)的实现方法

--处理表重复记录/**********************************************************************************************************************

linshenshijianlu / 0评论 2019-04-09

SQL SERVER 分组求和sql语句

SELECT A1,SUM FROM A GROUP BY A1大家可以自行测试一下，主要需要了解group by语句的用法

yushuir / 0评论 2019-04-09

MySQL与Oracle中分组、聚合函数的区别

今天需要这样一句sql：先用group by进行分组，然后利用聚合函数count 或者sum进行计算，并显示其它的辅助信息。) ENGINE=InnoDB DEFAULT CHARSET=utf8 房间表，seq房间入住序号（主键），rname为房间名，这

yehui之中期回顾 / 0评论 2010-01-19

sql server如何利用开窗函数over()进行分组统计

group by ProductType) t2 on t1.ProductType = t2.ProductType. ROW_NUMBER、DENSE_RANK、RANK、NTILE属于排名函数。PARTITION BY用于将结果集进行分组，开窗函数应

李轮清 / 0评论 2019-04-09

SQL进行排序、分组、统计的10个新技巧分享

SQL的ORDER BY语句可以以字母或数字顺序组织数据。因此，相似的值按组排序在一起。然而，这个分组时排序的结果，并不是真的分组。ORDER BY显示每条记录而分组可能代表很多记录。例如，下面的语句中的GROUP BY语句对数据源中重复出现的数据只返回唯

李轮清 / 0评论 2019-04-06

SQL分组排序去重复的小实例

row_bank = 1==========================PARTITIONBYuserid,classid 这里是按什么分组， 注意一下。

cjylean / 0评论 2019-04-06

Sql Server：多行合并成一行，并做分组统计的两个方法

CREATE TABLE test(code varchar(50), [values] varchar(10),[count] int). UNION ALL SELECT '002', 'ccc',3;SELECT code,stuff(b.[valu

李轮清 / 0评论 2019-04-06

Oracle数据库按时间进行分组统计数据的方法

select to_char,sum from table1 group by to_char;年份 数量。2010-40 88PS：Oracle按时间段分组统计想要按时间段分组查询，首先要了解level,connect by,oracle时间的加减

ihuaqiang / 0评论 2019-04-05

Sequelize中用group by进行分组聚合查询

SQL查询中，通GROUP BY语名实现分组查询。GROUP BY子句要和聚合函数配合使用才能完成分组查询，在SELECT查询的字段中，如果没有使用聚合函数就必须出现在ORDER BY子句中。分组查询后，查询结果为一个或多个列分组后的结果集。COUNT()

lanyuxinkong / 0评论 2019-04-05

SQL语句分组获取记录的第一条数据的方法

使用ROW_NUMBER() OVER 先进行分组 注：根据COL1分组，在分组内部根据 COL2排序，而此函数计算的值就表示每组内部排序后的顺序编号.可以看到是按照City分组，EmployeeID排序。

krzover / 0评论 2019-04-05

sql 分组查询问题

情景一： 表中数据 name score aaa 11 aaa 19 bbb 12 bbb 18 ccc 19 ddd 21 期望查询结果如下 name score aaa 30 bbb 30 ccc 19 ddd 21 代码如下：。select tnam

zhangdonghang / 0评论 2019-04-03

详解数据库中分类/分组并总计SQL数据

您需要了解如何使用某些SQL子句和运算符来安排SQL数据，从而对它进行高效分析。下面这些建议告诉您如何建立语句，获得您希望的结果。以有意义的方式安排数据可能是一种挑战。通常您必须进行更多处理——进行分组以利于分析与总计。可喜的是，SQL提供了大量用于分类、

执米之手与米偕老 / 0评论 2007-05-10

教你怎样分类、分组以及总计SQL数据

您需要了解如何使用某些SQL子句和运算符来安排SQL数据，从而对它进行高效分析。下面这些建议告诉您如何建立语句，获得您希望的结果。以有意义的方式安排数据可能是一种挑战。通常您必须进行更多处理——进行分组以利于分析与总计。可喜的是，SQL提供了大量用于分类、

jinmouliao0 / 0评论 2007-04-09

sql语句中where和having的区别

HAVING是先分组再筛选记录，WHERE在聚合前先筛选记录．也就是说作用在GROUP BY 子句和HAVING子句前；而 HAVING子句在聚合后对组记录进行筛选。作用的对象不同。WHERE 子句作用于表和视图，HAVING 子句作用于组。WHERE 在

zhoupc / 0评论 2019-04-01

[转]Mysql将数据分组后取出时间最近的数据

但是在我查出来的数据中似乎有些不对，如图，反白的那一条数据，Mark字段和CreateTime字段根本不对应啊！使用Max函数后在分组这样靠谱吗？求大神些一条SQL语句，能实现既不出现子句，又能查出正确数据。

网络游戏 / 0评论 2017-05-26

sql中的 where 、group by 和 having 用法解析

--如果要用到group by 一般用到的就是”每这个字 例如说明现在有一个这样的表：每个部门有多少人 就要用到分组的技术。select DepartmentID as '部门名称',COUNT(*) as '个数' from BasicDepartmen

网络游戏 / 0评论 2017-04-26

.Net程序员学用Oracle系列(21)：分组查询(GROUP BY)

1.1、GROUP BY 概述在分组查询中，GROUP BY 子句的作用就是按指定的列或表达式的值将选定行集进行分组，并针对每一组返回一行从组中收集到的数据。

89652511 / 0评论 2017-03-15