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

澳门新浦京娱乐场网站:SQL数据查询,GROUPING用法

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

GROUP_ID

GROUPING是多少个聚合函数,用在富含CUBE 或 ROLLUP 语句的SQL语句中,当结果聚焦的数据行是由CUBE 或 ROLLUP 运算产生的(增多的)则该函数重回壹,不然重临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合并,再统一tablea
d.借使要将统壹后的结果集保存到一个新数据表中,那么into语句必须投入到第二条select中
e.只可以够在最后一条select语句中利用 order by 和 compute 子句,那样影响到终极合并结果的排序和计数汇总
f.group by 和 having 子句能够在单身一个select查询中选拔,它们不影响最终结出
*/
--2 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 操作所生成的空值带来三个难点:怎样区分 CUBE 操作所生成的 NULL 值和从骨子里数目中回到的 NULL 值?
其1主题材料可用 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的女员工,只怕年龄小于等于二三的男职员和工人。

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 中的列名

2、相比较运算符

<>、!=
<、<= 、>、>=、!<、!>
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三-312虚岁时期的员工。


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
*/
--三 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,代表这是同1个分组的结果。

比如表明

创建表:

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重临一不是则重临0。那样1来他的用途就出去呀。

exists关键字

内定叁个子询问,测实施是还是不是存在

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 2二伍.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 操作并不报告左边1列(或左侧各列)中值的具有或许构成。比方,
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组选定行组合成2个摘要行集,针对每1组重返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() <1来剔除重复的分组。

compute子句

澳门新浦京娱乐场网站:SQL数据查询,GROUPING用法简介及说明。变动合计作为附加的汇聚列出现在结果集的尾声。当与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列前五条记下
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,因为最后1行是总的汇总,所以GROUPING的值为1.

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

 

GROUPING_ID

GROUPING_ID是GROUPING的巩固版,与GROUPING只好带二个表达式区别,它能带多个说明式。

语法如下:

GROUPING_ID(expr1, expr2, expr3,….)

下边我们来探望官方的分解:

澳门新浦京娱乐场网站 3

GROUPING_ID在效益上一定于将四个GROUPING函数的结果串接成二进制数,重返的是以此二进制数对应的十进制数。

下边我们来探望具体的案例:

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.

世家看看这么些案例估计都有点晕。。。

故此这么提供,是为着彰显1个直观的结果开始展览比较。

解读那么些结果,须求专注以下两点:

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

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

率先看率先列,第三列,尽管三个是grouping(deptno),五个是grouping_id(deptno),因为唯有三个表明式,所以两者的结果是一样的。第三列,第5列一样如此。

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

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

说了如此多,下边大家来看二个运用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> 

 

本文由澳门新浦京娱乐场网站发布于数据库,转载请注明出处:澳门新浦京娱乐场网站:SQL数据查询,GROUPING用法