当前位置: 56net亚洲必嬴 > 数据库 > 正文

mssql sqlserver 关键字 GROUPING用法简要介绍及表达

时间:2019-11-01 11:50来源:数据库
转自: http://www.maomao365.com/?p=6208   GROUP_ID GROUPING是贰个聚合函数,用在包蕴CUBE 或 ROLLUP语句的SQL语句中,当结果集中的数据行是由CUBE 或 ROLLUP运算发生的(增加的)则该函数重临1,不然

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

GROUP_ID

GROUPING是贰个聚合函数,用在包蕴CUBE 或 ROLLUP 语句的SQL语句中,当结果集中的数据行是由CUBE 或 ROLLUP 运算发生的(增加的)则该函数重临1,不然重临0。

询问是SQL语言的为主内容,而用于表示SQL查询的select语句,是SQL语句中效果最刚劲也是最复杂的话语。

摘要:
GROUPING 用于区分列是不是由 ROLLUP、CUBE 或 GROUPING SETS 聚合而发出的行
比方是原生态的行聚合,则再次回到0 ,新添的行数据就回来1

首先大家看看官方的表明:

语法: GROUPING ( column_name )   

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

必嬴56net 1

其中 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 雇员表

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

返回值
tinyint
<hr />
grouping 应用比方:  

大意是GROUP_ID用于区分相通分组标准的分组总括结果。

注意:

into子句

创制新表并以往自己检查询的结果行插入新表中
use 数据库
select
id,
age
into 新的雇员表
from 雇员表

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

表达起来相比较空虚,上边大家来探视现实的案例。

(1)只有利用了CUBE 或 ROLLUP 运算符的SQL中技巧动用GROUPING

where子句

1、逻辑运算符
not、and、or
use 数据库
select
name
sex
age
from 雇员表
where sex='女' and not age>=20
or sex='男' and age<=23
--查询雇员表不惑之年龄不当先等于20的女工作者,也许年龄小于等于23的男职员和工人。

 

例1:单一分组

(2)GROUPING 前面包车型大巴列 名可以是CUBE 或 ROLLUP 运算符中使用的列名,也足以是group by 中的列名

2、相比较运算符

<>、!=
<、<= 、>、>=、!<、!>
3、like关键字
%
_
[]
[^]
use 数据库
select
*
from 雇员表
where name like '%李'
or name like '王_必嬴56net,行'
and age like 2[2-4]
or age like 3[^3-4]
--查询雇员表中姓李,也许姓王某行,何况年龄在22-24大概年龄不在33-叁十三岁时期的工作者。

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

 

in 关键字

rollup(deptno)只是多少个唯生机勃勃的分组,所以发生的group_id()为0,代表那是同二个分组的结果。

举个例子表明

创建表:

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

all、some、any关键字

急需与相比较运算符和子查询一同行使

all,表示大于条件的每一个值,即抢先条件的最大值
some,表示大于条件的有个别值
any,表示起码抢先条件的意气风发值,即当先条件的最小值
use 数据库
select * from 雇员表
where age >all
(select age from 雇员表 where sex='男')

上边大家来看注重复分组的动静

(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。那样一来他的用途就出去啦。

exists关键字

点名二个子查询,测实践是不是留存

例2:重复分组

(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

group by子句

按多少个或三个列或表明式的值将后生可畏组选定行组合成一个摘要行集,针对每意气风发组重回豆蔻年华行。

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.

having子句

内定或聚合的查找条件
having只好与select语句一同利用。having日常在group by子句中选拔,纵然不利用group by 子句,则having的行事与where子句同样

group_id()为1代表这个是双重的分组。

order by子句

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

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

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

安贫乐道说,小编也看不出GROUP_ID在骨子里职业中的应用场景,姑且先记着。

distinct子句

编辑:数据库 本文来源:mssql sqlserver 关键字 GROUPING用法简要介绍及表达

关键词: