葡京网投哪个正规 > 新葡亰-数据 > SQL数据查询,GROUPING用法简介及说明

原标题:SQL数据查询,GROUPING用法简介及说明

浏览次数:83 时间:2019-11-01

grouping 语法简介 :
GROUPING (<列名>)
参数列名:

返回值
tinyint
<hr />
grouping 应用举例:  

GROUPING_ID(expr1, expr2, expr3,….)

GROUPING是一个聚合函数,用在含有CUBE 或 ROLLUP 语句的SQL语句中,当结果集中的数据行是由CUBE 或 ROLLUP 运算产生的(添加的)则该函数返回1,否则返回0。

having子句

指定或聚合的搜索条件
having只能与select语句一起使用。having通常在group by子句中使用,如果不使用group by 子句,则having的行为与where子句一样

create table test(info varchar(30))
go
insert into test (info)values('a'),
('b'),('a'),('c'),('d'),('d') 
go

select info,count_big(info),grouping(info)
from test group by info 
WITH ROLLUP

go
drop table test 
----输出----
ifno 无列名 无列名
a    2    0
b    1    0
c    1    0
d    2    0
NULL    6    1

第五列的结果是第一列和第二列的数值的串接,然后返回的十进制数,以第二行为例,GI_DJ=2其实是二进制10转化为十进制后的数,其中1为G_D的值,0为G_J的值。

举例说明

创建表:

CREATE TABLE DEPART (部门 char(10),员工 char(6),工资 int)

INSERT INTO DEPART SELECT 'A','ZHANG',100
INSERT INTO DEPART SELECT 'A','LI',200
INSERT INTO DEPART SELECT 'A','WANG',300
INSERT INTO DEPART SELECT 'A','ZHAO',400
INSERT INTO DEPART SELECT 'A','DUAN',500
INSERT INTO DEPART SELECT 'B','DUAN',600
INSERT INTO DEPART SELECT 'B','DUAN',700

表中数据:

部门         员工         工资

A             ZHANG     100
A             LI             200
A             WANG      300
A             ZHAO      400
A             DUAN      500
B             DUAN      600
B             DUAN      700

order by子句

降序:oder by ……desc
升序:oder by ……asc

 

GROUPING_ID是GROUPING的增强版,与GROUPING只能带一个表达式不同,它能带多个表达式。

 

distinct子句

转自: http://www.maomao365.com/?p=6208  

葡京网投哪个正规 1

(1)GROUPING的作用

A:先执行一个ROLLUP,看看结果 以便对比

SELECT 部门,员工,SUM(工资)AS TOTAL
FROM DEPART
GROUP BY  部门,员工  WITH ROLLUP

结果:

部门         员工        TOTAL

A             DUAN       500
A             LI             200
A             WANG      300
A             ZHANG     100
A             ZHAO       400
A             NULL       1500
B             DUAN      1300
B             NULL       1300
NULL      NULL        2800

B:在A  的基础上 加上GROUPING ,执行下面的SQL(GROUPING中的列名是ROLLUP的列名)

SELECT 部门,员工,SUM(工资)AS TOTAL,GROUPING(员工) AS 'Grouping'
FROM DEPART
GROUP BY  部门,员工  WITH ROLLUP

结果:

部门         员工        TOTAL  Grouping

A             DUAN      500         0
A             LI             200        0
A             WANG      300        0
A             ZHANG     100         0
A             ZHAO      400          0
A             NULL        1500       1
B             DUAN      1300        0
B             NULL       1300       1
NULL       NULL       2800       1

C: 在A 的基础上 加上GROUPING ,执行下面的SQL(GROUPING中的列名是GROUP BY后的列名,但不是ROLLUP的列名)

SELECT 部门,员工,SUM(工资)AS TOTAL,GROUPING(部门) AS 'Grouping'
FROM DEPART
GROUP BY  部门,员工  WITH ROLLUP

结果:

部门         员工        TOTAL  Grouping

A             DUAN      500         0
A             LI             200        0
A             WANG      300        0
A             ZHANG     100        0
A             ZHAO      400         0
A             NULL       1500       0
B             DUAN      1300       0
B             NULL     1300        0
NULL       NULL     2800        1

看到了没?GROUPING就是用来测试结果集中的那些数据是CUBE 或 ROLLUP添加进去的,是则 GROUPIN返回1不是则返回0。这样一来他的用处就出来啦。

group by子句

按一个或多个列或表达式的值将一组选定行组合成一个摘要行集,针对每一组返回一行。


with t as
   ( select grouping_id(deptno,job)gi_dj,deptno,job,count(*)cnt
     from emp group by cube(deptno,job)),
t1 as
  ( select decode(gi_dj,0,deptno,1,deptno,99) deptno,decode(gi_dj,1,cnt,3,cnt)sub_total,
          decode(job,'CLERK',cnt) c1,decode(job,'ANALYST',cnt)c2,decode(job,'MANAGER',cnt)c3,
          decode(job,'SALESMAN',cnt)c4,decode(job,'PRESIDENT',cnt)c5 
    from t)
select deptno,max(sub_total) sub_total,max(c1)clerk,max(c2)analyst,
              max(c3)manager,max(c4)salesman,max(c5)president 
from t1 group by deptno order by deptno;

语法: GROUPING ( column_name )   

into子句

创建新表并将来自查询的结果行插入新表中
use 数据库
select
id,
age
into 新的雇员表
from 雇员表

摘要:
GROUPING 用于区分列是否由 ROLLUP、CUBE 或 GROUPING SETS 聚合而产生的行
如果是原生态的行聚合,则返回0 ,新增的行数据就返回1

GROUPING_ID在功能上相当于将多个GROUPING函数的结果串接成二进制数,返回的是这个二进制数对应的十进制数。

(2)GROUPING用法

可以用在HAVING语句中,用去选取或去掉合计值,对比上面的执行结果看看下面的执行结果 ,你就什么都明白了。

SELECT 部门,员工,SUM(工资)AS TOTAL
FROM DEPART
GROUP BY  部门,员工  WITH ROLLUP
HAVING GROUPING(员工)=1

结果:

葡京网投哪个正规,部门         员工        TOTAL

A             NULL    1500
B             NULL    1300
NULL        NULL    2800

SELECT 部门,员工,SUM(工资)AS TOTAL
FROM DEPART
GROUP BY  部门,员工  WITH ROLLUP
HAVING GROUPING(员工)=0

结果:

部门         员工        TOTAL

A             DUAN      500
A             LI             200
A             WANG      300
A             ZHANG     100
A             ZHAO      400
B             DUAN      1300

SELECT 部门,员工,SUM(工资)AS TOTAL
FROM DEPART
GROUP BY  部门,员工  WITH ROLLUP
HAVING GROUPING(部门) =1

结果:

部门         员工        TOTAL

NULL      NULL         2800

where子句

1、逻辑运算符
not、and、or
use 数据库
select
name
sex
age
from 雇员表
where sex='女' and not age>=20
or sex='男' and age<=23
--查询雇员表中年龄不大于等于20的女员工,或者年龄小于等于23的男员工。

3> 

注意:

查询是SQL语言的中心内容,而用于表示SQL查询的select语句,是SQL语句中功能最强大也是最复杂的语句。

参考资料:

其中 column_name 是用在CUBE 或 ROLLUP 运算的列 或group by 后的列。

select……from子句

该语句常用的行聚合函数有

  • count(*),返回组中的项数
  • count({ [ [all|distinct] 列名]葡京正网网投, }),返回某列的个数
  • avg({ [ [all|distinct] 列名] }),返回某列的平均值
  • max({ [ [all|distinct] 列名] }),返回某列的最大值
  • min({ [ [all|distinct] 列名] }),返回某列的最小值
  • sum({ [ [all|distinct] 列名] }),返回某列的和
    取别名可用三种方法
  • 别名=列名
  • 列名 as 别名
  • 列名 别名
    举例:
    use 数据库
    select
    distinct 员工编号=id
    name as 姓名
    sex 性别
    from 雇员表

下面我们来看看官方的解释:

(1)只有使用了CUBE 或 ROLLUP 运算符的SQL中才能使用GROUPING

exists关键字

指定一个子查询,测试行是否存在

语法如下:

(2)GROUPING 后面的列 名可以是CUBE 或 ROLLUP 运算符中使用的列名,也可以是group by 中的列名

with子句

用于指定临时命名的结果集,这些结果集称为公用表表达式(CTE)。
该表达书源自简单查询,并且在单条select、insert、update、delete语句的执行范围内定义。
语法格式:

              [ with 指定临时命名的结果集 [,……n] ]
              指定临时命名的结果集>::=
                      公用表表达书的有效标识符[ (在公用表达式中的指定列名[,……])]
                as
                     (指定一个其结果集填充公用表达式的select语句)

举例:
创建公用表表达式,计算雇员数据表中年龄字段中每一年龄员工的数量。

               use 数据库
                with agereps(age,agecount) as
              (
                   select 
                     age,
                     count(*)
                  from 雇员表 as agereports
                  where age is not null
                  group by age     
             )
               select  age,agecount
               from agereps

创建公用表表达式,计算雇员数据表中员工age的平均值

              use 数据库
              with avgagereps(age,agecount) as
              (
              select 
                  age,
                  count(*)
              from 雇员表 as agereports
              where age is not null
              group by age
              )
             select avg(age) as [avgage of 雇员表]
             from avgagereps

之所以这样提供,是为了呈现一个直观的结果进行对比。

2、比较运算符

<>、!=
<、<= 、>、>=、!<、!>
3、like关键字
%
_
[]
[^]
use 数据库
select
*
from 雇员表
where name like '%李'
or name like '王_行'
and age like 2[2-4]
or age like 3[^3-4]
--查询雇员表中姓李,或者姓王某行,并且年龄在22-24或者年龄不在33-34岁之间的员工。

首先我们看看官方的解释:

compute子句

生成合计作为附加的汇总列出现在结果集的最后。当与by一起使用时,compute子句在结果集内生成控制中断和小计。

  • compute子句可以使用行聚合函数,如avg/count/max/min/sum/stdev(标准差)/stdevp(总体标准差)/var(方差)/varp
  • 如果用compute子句指定行聚合函数,则不能用distinct关键字;
    区别:
    use 数据库
    select * from 雇员表
    order by sex
    compute avg(age)

    use 数据库
    select * from 雇员表
    order by sex
    compute avg(age) by sex

解释起来比较抽象,下面我们来看看具体的案例。

all、some、any关键字

需要与比较运算符和子查询一起使用

all,表示大于条件的每一个值,即大于条件的最大值
some,表示大于条件的一些值
any,表示至少大于条件的一值,即大于条件的最小值
use 数据库
select * from 雇员表
where age >all
(select age from 雇员表 where sex='男')

1> 

in 关键字

GROUPING

top子句

限制查询结果集的行数。
举例:查询雇员表中name/age列前五条记录
use 数据库
select top 5 name,age from 雇员表

下面我们来看看具体的案例:

GROUP_ID

首先看第一列,第三列,虽然一个是grouping(deptno),一个是grouping_id(deptno),因为只有一个表达式,所以两者的结果是一样的。第二列,第四列同样如此。

SQL> select grouping(deptno)g_d,grouping(job)g_j,grouping_id(deptno)gi_d,grouping_id(job)gi_j,grouping_id(deptno,job)gi_dj,grouping_id(job,deptno)gi_jd,deptno,job,sum(sal) from emp group by cube(deptno,job);

       G_D        G_J        GI_D       GI_J      GI_DJ      GI_JD    DEPTNO  JOB         SUM(SAL)
---------- ----------  ---------- ---------- ---------- ---------- ---------- --------- ----------
         1          1           1          1          3          3                           29025
         1          0           1          0          2          1            CLERK           4150
         1          0           1          0          2          1            ANALYST         6000
         1          0           1          0          2          1            MANAGER         8275
         1          0           1          0          2          1            SALESMAN        5600
         1          0           1          0          2          1            PRESIDENT       5000
         0          1           0          1          1          2         10                 8750
         0          0           0          0          0          0         10 CLERK           1300
         0          0           0          0          0          0         10 MANAGER         2450
         0          0           0          0          0          0         10 PRESIDENT       5000
         0          1           0          1          1          2         20                10875
         0          0           0          0          0          0         20 CLERK           1900
         0          0           0          0          0          0         20 ANALYST         6000
         0          0           0          0          0          0         20 MANAGER         2975
         0          1           0          1          1          2         30                 9400
         0          0           0          0          0          0         30 CLERK            950
         0          0           0          0          0          0         30 MANAGER         2850
         0          0           0          0          0          0         30 SALESMAN        5600

18 rows selected.
SQL> select group_id(),deptno,sum(sal) from emp group by rollup(deptno,deptno);

GROUP_ID()      DEPTNO    SUM(SAL)
----------  ---------- ----------
         0         10        8750
         0         20       10875
         0         30        9400
         1         10        8750
         1         20       10875
         1         30        9400
         0                  29025

7 rows selected.

即GROUPING函数用于区分分组后的普通行和聚合行。如果是聚合行,则返回1,反之,则是0。

 

group_id()为1代表这些是重复的分组。

说了这么多,下面我们来看一个利用GROUPING_ID实现行列转换的案例。

2> 

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

GROUP_ID()     DEPTNO    SUM(SAL)
---------- ----------  ----------
         0         10        8750
         0         20       10875
         0         30        9400
         0                  29025

葡京网投哪个正规 2

2> GROUPING_ID(expr1, expr2, expr3,….)的值其实是对应GROUPING(expr1),GROUPING(expr2),GROUPING(expr3)...值的串接。

其中,99代表合计,sub_total代表小计。这种统计类的需求在实际生产中还是应用蛮广的。

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

GROUPING(DEPTNO) GROUPING(JOB)       DEPTNO JOB          SUM(SAL)
---------------- -------------   ---------- ---------  ----------
               0             0           10 CLERK            1300
               0             0           10 MANAGER          2450
               0             0           10 PRESIDENT        5000
               0             1           10                  8750
               0             0           20 CLERK            1900
               0             0           20 ANALYST          6000
               0             0           20 MANAGER          2975
               0             1           20                 10875
               0             0           30 CLERK             950
               0             0           30 MANAGER          2850
               0             0           30 SALESMAN         5600
               0             1           30                  9400
               1             1                              29025

13 rows selected.

大意是GROUP_ID用于区分相同分组标准的分组统计结果。

GROUPING_ID

    DEPTNO  SUB_TOTAL      CLERK     ANALYST    MANAGER     SALESMAN  PRESIDENT
---------- ----------  ----------  --------- ----------   ---------- ----------
        10          3           1                     1                       1
        20          5           2          2          1
        30          6           1                     1            4
        99         14           4          2          3            4          1

首先我们看GROUPING(DEPTNO)这一列的结果,不难看出,凡是基于DEPTNO的汇总,GROUPING的结果均为0,因为最后一行是总的汇总,所以GROUPING的值为1.

with t as(select grouping_id(deptno,job)gi_dj,deptno,job,count(*)cnt from emp group by cube(deptno,job)),
t1 as (select decode(gi_dj,0,deptno,1,deptno,99)deptno,decode(gi_dj,0,job,2,job,9)job,cnt from t)
select * from (select * from t1)pivot(sum(cnt)for job in ('9','CLERK','ANALYST','MANAGER','SALESMAN','PRESIDENT')) order by deptno;

基于这个逻辑,可以看出GROUPING(JOB)的值也是吻合的。

 

下面我们来看看重复分组的情况

其语法为:GROUPING(expr)

注意:可通过having group_id() <1来剔除重复的分组。

葡京网投哪个正规 3

rollup(deptno)只是一个唯一的分组,所以产生的group_id()为0,代表这是同一个分组的结果。

下面我们来看看具体的案例:

大家看到这个案例估计都有点晕。。。

当然,该结果也可利用PIVOT函数实现,具体语句如下:

解读这个结果,需要注意以下两点:

最后生成的结果如下:

例2:重复分组

例1:单一分组

下面我们来看看官方的解释:

 

老实说,我也看不出GROUP_ID在实际工作中的应用场景,姑且先记着。

而GI_JD=1则是二进制01转化为十进制后的数,其中0为G_J的值,1为G_D的值。注意,串接的顺序为GROUPING_ID中表达式的顺序。

1> 若本行是某expr的汇总,那么该expr对应的二进制数位置为0否则置为1。

本文由葡京网投哪个正规发布于新葡亰-数据,转载请注明出处:SQL数据查询,GROUPING用法简介及说明

关键词:

上一篇:Python全栈开发葡京网投哪个正规:,mysql进阶新手司机

下一篇:解决MySQL中IN子查询会导致无法使用索引问题,MySQL去除重复数据【葡京正网网投】