澳门新浦京娱乐场网站-www.146.net-新浦京娱乐场官网
做最好的网站

cumpute运算符使用表达,GROUPING用法简要介绍及表

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

GROUP_ID

GROUPING是3个聚合函数,用在含有CUBE 或 ROLLUP 语句的SQL语句中,当结果聚集的数据行是由CUBE 或 ROLLUP 运算产生的(增加的)则该函数再次回到1,不然重临0。

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

/*
--一 UNION 运算符是将多个或更加多询问的结果组合为单个结果集
动用 UNION 组合查询的结果集有八个最基本的条条框框:
一。全体查询中的列数和列的相继必须1致。
二。数据类型必须合营
a.UNION的结果集列名与第3个select语句中的结果集中的列名一样,其余select语句的结果集列名被忽略
b.默许景况下,UNION 运算符是从结果聚集删除重复行。要是运用all关键字,那么结果集将含有全数行并且不删除重复行
c.sql是从左到右对包涵UNION 运算符的口舌进行取值,使用括号能够变动求值顺序
--例如:
*/
select * from tablea
union all
(
select * from tableb
union all
select * from tablec
)
/*
这么就足以先对tableb和tablec合并,再统1tablea
d.假使要将联合后的结果集保存到三个新数据表中,那么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 ,新扩展的行数据就回来一

第一大家看看官方的批注:

语法: 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 操作所生成的空值带来2个主题素材:怎么着区分 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 #cumpute运算符使用表达,GROUPING用法简要介绍及表达。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

释疑起来比较空虚,上面大家来探视实际的案例。

(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的女职员和工人,或然年龄低于等于二三的男职员和工人。

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 语句恐怕生成比不小的结果集,因为那些语句会为具备维度中值的具有组合生成行。
那一个大结果集带有的多寡可能过多而不便于阅读和通晓。那个主题材料有壹种消除办法是将 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:单一分组

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

二、相比较运算符

<>、!=
<、<= 、>、>=、!<、!>
3、like关键字
%
_
[]
[^]
use 数据库
select
*
from 雇员表
where name like '%李'
or name like '王_行'
and age like 2[2-4]
or age like 3[^3-4]
--查询雇员表中姓李,只怕姓王某行,并且年龄在2二-贰四恐怕年龄不在3三-三十四周岁之间的职工。


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 生成的结果集浮现了所选列中值的某一档期的顺序结构的见面。 举个例子,轻松表 #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,表示大于条件的1对值
any,表示至少超过条件的1值,即超越条件的最小值
use 数据库
select * from 雇员表
where age >all
(select age from 雇员表 where sex='男')


上面大家来探望重复分组的地方

(1)GROUPING的作用

A:先实行2个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。那样1来他的用途就出去啦。

exists关键字

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

Chair Blue 101
Chair Red 210
Chair ALL 311
Table Blue 124
Table Red 223
Table ALL 347
ALL ALL 658
(七 個資料列受到影響)
*/
/*
假定查询中的 ROLLUP 关键字改成为 CUBE,那么 CUBE 结果集与上述结果1致,只是在结果集的结尾还会回去下列两行:ALL Blue 225.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子句

按贰个或三个列或表明式的值将一组选定行组合成二个摘要行集,针对每一组再次来到一行。

你可能感兴趣的篇章:

  • 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函数用于区分分组后的普通行和聚合行。假设是聚合行,则赶回一,反之,则是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的值为壹.

依照那几个逻辑,可以看到GROUPING(JOB)的值也是切合的。

 

GROUPING_ID

GROUPING_ID是GROUPING的加强版,与GROUPING只好带一个表达式分化,它能带三个表达式。

语法如下:

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.

世家收看这么些案例推断都有点晕。。。

为此这么提供,是为着呈现3个直观的结果实行自己检查自纠。

解读那一个结果,供给专注以下两点:

1> 若本行是某expr的聚集,那么该expr对应的2进制数地点为0不然置为壹。

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

先是看率先列,第一列,尽管贰个是grouping(deptno),二个是grouping_id(deptno),因为唯有1个表明式,所以两者的结果是壹致的。第一列,第陆列同样如此。

第5列的结果是率先列和第三列的数值的串接,然后回到的10进制数,以第二行为例,GI_DJ=2其实是贰进制10转速为十进制后的数,个中壹为G_D的值,0为G_J的值。

而GI_JD=一则是贰进制01转化为10进制后的数,个中0为G_J的值,1为G_D的值。注意,串接的逐一为GROUPING_ID中表明式的各类。

说了那样多,上边大家来看1个运用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运算符使用表达,GROUPING用法简要介绍及表