澳门新萄京官方网站-www.8455.com-澳门新萄京赌场网址

澳门新萄京官方网站:cumpute运算符使用说明,

2019-06-01 作者:数据库网络   |   浏览(199)

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

GROUP_ID

GROUPING是二个聚合函数,用在含有CUBE 或 ROLLUP 语句的SQL语句中,当结果集中的数据行是由CUBE 或 ROLLUP 运算发生的(加多的)则该函数重返一,不然重返0。

询问是SQL语言的基本内容,而用于表示SQL查询的select语句,是SQL语句中作用最有力也是最复杂的口舌。

/*
--1 UNION 运算符是将三个或更加的多询问的结果组合为单个结果集
利用 UNION 组合查询的结果集有多少个最主旨的条条框框:
1。全部查询中的列数和列的次第必须一律。
二。数据类型必须同盟
a.UNION的结果集列名与第二个select语句中的结果聚集的列名一样,其余select语句的结果集列名被忽视
b.暗许情状下,UNION 运算符是从结果集中删除重复行。如若使用all关键字,那么结果集将涵盖全部行并且不删除重复行
c.sql是从左到右对含有UNION 运算符的讲话进行取值,使用括号能够改换求值顺序
--例如:
*/
select * from tablea
union all
(
select * from tableb
union all
select * from tablec
)
/*
这么就能够先对tableb和tablec合并,再统一tablea
d.假诺要将联合后的结果集保存到3个新数据表中,那么into语句必须投入到第壹条select中
e.只好够在结尾一条select语句中央银行使 order by 和 compute 子句,那样影响到终极合并结果的排序和计数汇总
f.group by 和 having 子句能够在单独二个select查询中动用,它们不影响最后结果
*/
--二 CUBE 汇总量据
/*
CUBE 运算符生成的结果集是多维数据集。多维数据集是事实数据的恢宏,事实数据即记录个别事件的数目。
扩充建构在用户盘算深入分析的列上。这么些列被称为维。多维数据集是多少个结果集,个中积存了各维度的兼具也许构成的交叉表格。
CUBE 运算符在 SELECT 语句的 GROUP BY 子句中内定。该语句的取舍列表应涵盖维度列和聚合函数表明式。
GROUP BY 应指定维度列和注重字 WITH CUBE。结果集将含有维度列中各值的具有大概构成,以及与这一个维度值组合相相配的底子行中的聚合值。
*/
--下列查询重回的结果聚集,将包罗 Item 和 Color 的享有十分大可能率构成的 Quantity 小计:
-->Title:生成測試數據
-->Author:wufeng4552
-->Date :2009-09-10 14:36:20
if not object_id('Tempdb..#t') is null
drop table #t
Go
Create table #t([Item] nvarchar(5),[Color] nvarchar(4),[Quantity] int)
Insert #t
select N'Table',N'Blue',124 union all
select N'Table',N'Red',223 union all
select N'Chair',N'Blue',101 union all
select N'Chair',N'Red',210
Go
select [Item],
[Color],
sum([Quantity])[Quantity]
from #t group by [Item],[Color] with cube
/*
Item Color Quantity

摘要:
GROUPING 用于区分列是还是不是由 ROLLUP、CUBE 或 GROUPING SETS 聚合而产生的行
若是果原生态的行聚合,则重返0 ,新扩大的行数据就回去1

第3大家看看官方的分解:

语法: 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


澳门新萄京官方网站 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 雇员表

Chair Blue 101
Chair Red 210
Chair NULL 311
Table Blue 124
Table Red 223
Table NULL 347
NULL NULL 658
NULL Blue 225
NULL Red 433
*/
/*CUBE 操作所生成的空值带来1个难题:如何区分 CUBE 操作所生成的 NULL 值和从骨子里多少中回到的 NULL 值?
那么些标题可用 GROUPING 函数消除。
壹经列中的值来自事实数据,则 GROUPING 函数重临 0;若是列中的值是 CUBE 操作所生成的 NULL,则赶回 一。
在 CUBE 操作中,所生成的 NULL 代表整个值。可将 SELECT 语句写成选择GROUPING 函数将所生成的 NULL 替换为字符串 ALL。
因为实际数据中的 NULL 申明数据值未知,所以 SELECT 语句还可译码为回去字符串 UNKNOWN 替代来自事实数据的 NULL。
例如:
*/
-->Title:生成測試數據
澳门新萄京官方网站,-->Author:wufeng4552
-->Date :2009-09-10 14:36:20
if not object_id('Tempdb..#t') is null
drop table #t
Go
Create table #t([Item] nvarchar(5),[Color] nvarchar(4),[Quantity] int)
Insert #t
select N'Table',N'Blue',124 union all
select N'Table',N'Red',223 union all
select N'Chair',N'Blue',101 union all
select N'Chair',N'Red',210
Go
select [Item]=case when grouping([Item])=1 then 'ALL' else isnull(Item, 'UNKNOWN')end,
[Color]=case when grouping([Color])=1 then 'ALL' else isnull([Color],'UNKNOWN')end,
sum([Quantity])[Quantity]
from #t group by [Item],[Color] with cube
/*
Item Color Quantity

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

表达起来相比较空虚,上边我们来看望具体的案例。

(壹)唯有利用了CUBE 或 ROLLUP 运算符的SQL中技能动用GROUPING

where子句

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

Chair Blue 101
Chair Red 210
Chair ALL 311
Table Blue 124
Table Red 223
Table ALL 347
ALL ALL 658
ALL Blue 225
ALL Red 433
(九 個資料列受到影響)
*/
/*
涵盖带有繁多维度的 CUBE 的 SELECT 语句大概生成相当大的结果集,因为那么些语句会为具备维度中值的兼具组合生成行。
那个大结果集带有的数目大概过多而不轻易阅读和清楚。这些主题材料有1种化解办法是将 SELECT 语句放在视图中:
*/
create view view_cube
as
select [Item]=case when grouping([Item])=1 then 'ALL' else isnull(Item, 'UNKNOWN')end,
[Color]=case when grouping([Color])=1 then 'ALL' else isnull([Color],'UNKNOWN')end,
sum([Quantity])[Quantity]
from tb group by [Item],[Color] with cube --視圖中不可能用臨時表,故改之
--然后就可以用该视图来只询问您感兴趣的维度值:
SELECT *
FROM InvCube
WHERE Item = 'Chair' AND Color = 'ALL'
/*
Item Color QtySum

 

例1:单1分组

(二)GROUPING 前边的列 名能够是CUBE 或 ROLLUP 运算符中使用的列名,也能够是group by 中的列名

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-二四恐怕年龄不在3三-三14虚岁之间的职员和工人。


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 关键字

Chair ALL 311.00
*/
--3 ROLLUP 汇总量据
/*
用 ROLLUP 汇总量据在转移包括小计和合计的报表时,ROLLUP 运算符很有用。
ROLLUP 运算符生成的结果集类似于 CUBE 运算符所生成的结果集。
CUBE 和 ROLLUP 之间的分别在于: CUBE 生成的结果集显示了所选列中值的有所组成的聚合。
ROLLUP 生成的结果集浮现了所选列中值的某1等级次序结构的聚众。 譬如,轻便表 #t
中包含:Item Color Quantity
*/
select [Item]=case when grouping([Item])=1 then 'ALL' else isnull(Item, 'UNKNOWN')end,
[Color]=case when grouping([Color])=1 then 'ALL' else isnull([Color],'UNKNOWN')end,
sum([Quantity])[Quantity]
from #t group by [Item],[Color] with rollup
/*
Item Color Quantity

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:先举行3个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再次来到一不是则再次回到0。那样一来他的用途就出去啦。

exists关键字

点名贰个子询问,测施行是或不是留存

Chair Blue 101
澳门新萄京官方网站:cumpute运算符使用说明,SQL数据查询。Chair Red 210
Chair ALL 311
Table Blue 124
Table Red 223
Table ALL 347
ALL ALL 658
(7 個資料列受到影響)
*/
/*
一经查询中的 ROLLUP 关键字改成为 CUBE,那么 CUBE 结果集与上述结果一致,只是在结果集的末尾还只怕会重返下列两行:ALL Blue 2二5.00
ALL Red 433.00
CUBE 操作为 Item 和 Color 中值的或是组合生成行。
诸如,CUBE 不惟有报告与 Item 值 Chair 相组合的 Color 值的富有望构成(Red、Blue 和 Red Blue),
并且告诉与 Color 值 Red 相组合的 Item 值的保有极大恐怕构成(Chair、Table 和 Chair Table)。
对此 GROUP BY 子句中左边的列中的每一个值,ROLLUP 操作并不告诉右侧一列(或左侧各列)中值的兼具大概构成。举个例子,
ROLLUP 并不对每种 Color 值报告 Item 值的具有或然构成。
ROLLUP 操作的结果集全数类似于 COMPUTE BY 所重返结果集的功力;不过,ROLLUP 具备下列优点: ROLLUP 再次回到单个结果集;COMPUTE BY 再次来到多个结实集,而多少个结实集会增添应用程序代码的纷纭。
ROLLUP 能够在服务器游标中利用;COMPUTE BY 不得以。
不常,查询优化器为 ROLLUP 生成的进行安插比为 COMPUTE BY 生成的尤为便捷。
*/

例贰:重复分组

(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子句

按二个或多少个列或表明式的值将1组选定行组合成3个摘要行集,针对每壹组重返1行。

你可能感兴趣的篇章:

  • SQLServer中汇总成效的接纳GROUPING,ROLLUP和CUBE
  • SQLserver中cube:多维数据集实例详解
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()为一象征那些是重新的分组。

order by子句

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

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

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子句

 

top子句

界定查询结果集的行数。
譬释尊讲:查询雇员表中name/age列前5条记下
use 数据库
select top 5 name,age from 雇员表

GROUPING

其语法为:GROUPING(expr)

下边我们来探望官方的表达:

澳门新萄京官方网站 2

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

上面大家来探望现实的案例:

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.

先是大家看GROUPING(DEPTNO)那1列的结果,简单看出,凡是基于DEPTNO的汇总,GROUPING的结果均为0,因为最终一行是总的汇总,所以GROUPING的值为1.

据说那么些逻辑,能够见见GROUPING(JOB)的值也是符合的。

 

GROUPING_ID

GROUPING_ID是GROUPING的加强版,与GROUPING只好带1个表明式分裂,它能带四个表明式。

语法如下:

GROUPING_ID(expr1, expr2, expr3,….)

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

澳门新萄京官方网站 3

GROUPING_ID在成效上也便是将四个GROUPING函数的结果串接成2进制数,重临的是以此贰进制数对应的拾进制数。

上面大家来看望实际的案例:

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.

世家看来那么些案例猜度都有一点点晕。。。

故而这样提供,是为着表现三个直观的结果开始展览相比。

解读那几个结果,须求小心以下两点:

一> 若本行是某expr的汇集,那么该expr对应的2进制数地方为0否则置为1。

2> GROUPING_ID(expr一, expr贰, expr叁,….)的值其实是对应GROUPING(expr一),GROUPING(expr2),GROUPING(expr三)...值的串接。

第2看率先列,第二列,纵然3个是grouping(deptno),2个是grouping_id(deptno),因为唯有二个表明式,所以双方的结果是壹律的。第一列,第5列同样如此。

第4列的结果是首先列和第1列的数值的串接,然后回到的十进制数,以第2表现例,GI_DJ=2其实是二进制十转会为十进制后的数,个中壹为G_D的值,0为G_J的值。

而GI_JD=1则是二进制01转化为10进制后的数,当中0为G_J的值,1为G_D的值。注意,串接的依次为GROUPING_ID中表明式的相继。

说了这般多,上边大家来看3个施用GROUPING_ID达成行列转变的案例。

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;

末段生成的结果如下:

    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

里头,9九意味着协商,sub_total代表小计。这种总计类的需要在实质上生育中依旧采取蛮广的。

理之当然,该结果也可应用PIVOT函数落成,具身体语言句如下:

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;

仿效资料:

1> 

2> 

3> 

 

本文由澳门新萄京官方网站发布于数据库网络,转载请注明出处:澳门新萄京官方网站:cumpute运算符使用说明,

关键词: