SQL Server2008 程序设计汇总 GROUP BY，WITH ROLLUP，WITH CUBE，GROUPING SETS(..)-白红宇

SQL Server2008 程序设计汇总 GROUP BY，WITH ROLLUP，WITH CUBE，GROUPING SETS(..)

阅读量：6710 次

发布时间：2019-06-25

本文共 11762 字，大约阅读时间需要 39 分钟。

--SQL Server2008 程序设计汇总 GROUP BY ,WITH ROLLUP WITH CUBE GROUPING SET(..)

/********************************************************************************

*主题：SQL Server2008 程序设计汇总 group by ,WITH ROLLUP WITH CUBE

*说明：本文是个人学习的一些笔记和个人愚见

* 有很多地方你可能觉得有异议，欢迎一起讨论

*作者：Stephenzhou(阿蒙)

*日期: 2012.12.5

*Mail:szstephenzhou@163.com

*另外：转载请著名出处。

**********************************************************************************/

--SQL Server2008 程序设计 汇总 group by ,WITH ROLLUP  WITH CUBE         /********************************************************************************           *主题：SQL Server2008 程序设计 汇总 group by ,WITH ROLLUP  WITH CUBE    *说明：本文是个人学习的一些笔记和个人愚见           *      有很多地方你可能觉得有异议，欢迎一起讨论                     *作者：Stephenzhou(阿蒙)           *日期: 2012.12.5         *Mail:szstephenzhou@163.com           *另外：转载请著名出处。          **********************************************************************************/

以下是测试数据

IF OBJECT_ID('Inventory') is not null

drop table Inventory

create table Inventory(

Store varchar(2),

Item varchar(20),

Color varchar(10),

Quantity decimal

)

insert into Inventory values('NY','Table','Blue',124)

insert into Inventory values('NJ','Table','Blue',100)

insert into Inventory values('NY','Table','Red',29)

insert into Inventory values('NJ','Table','Red',56)

insert into Inventory values('PA','Table','Red',138)

insert into Inventory values('NY','Table','Green',229)

insert into Inventory values('PA','Table','Green',304)

insert into Inventory values('NY','Chair','Blue',101)

insert into Inventory values('NJ','Chair','Blue',22)

insert into Inventory values('NY','Chair','Red',21)

insert into Inventory values('NJ','Chair','Red',10)

insert into Inventory values('PA','Chair','Red',136)

insert into Inventory values('NJ','Sofa','Green',2)

IF OBJECT_ID('Inventory') is not nulldrop table Inventorygocreate table Inventory(Store varchar(2),Item varchar(20),Color varchar(10),Quantity decimal )insert into Inventory values('NY','Table','Blue',124)insert into Inventory values('NJ','Table','Blue',100)insert into Inventory values('NY','Table','Red',29)insert into Inventory values('NJ','Table','Red',56)insert into Inventory values('PA','Table','Red',138)insert into Inventory values('NY','Table','Green',229)insert into Inventory values('PA','Table','Green',304)insert into Inventory values('NY','Chair','Blue',101)insert into Inventory values('NJ','Chair','Blue',22)insert into Inventory values('NY','Chair','Red',21)insert into Inventory values('NJ','Chair','Red',10)insert into Inventory values('PA','Chair','Red',136)insert into Inventory values('NJ','Sofa','Green',2)

--一般的group by

select Item,Color,SUM(Quantity) as TotalQuantity,COUNT(Store) as Stores

from Inventory

group by Item,Color

order by Item,Color

Item Color TotalQuantity Stores

-------------------- ---------- --------------------------------------- -----------

Chair Blue 123 2

Chair Red 167 3

Sofa Green 2 1

Table Blue 224 2

Table Green 533 2

Table Red 223 3

(6 行受影响)

select Item,Color,SUM(Quantity) as TotalQuantity,COUNT(Store) as Stores from Inventory group by  Item,Color order by Item,Color/*Item                 Color      TotalQuantity                           Stores-------------------- ---------- --------------------------------------- -----------Chair                Blue       123                                     2Chair                Red        167                                     3Sofa                 Green      2                                       1Table                Blue       224                                     2Table                Green      533                                     2Table                Red        223                                     3(6 行受影响)*/

GROUP BY .. WITH ROLLUP

多了4个rollup行

select Item,Color,SUM(Quantity) as TotalQuantity,COUNT(Store) as Stores

from Inventory

group by Item,Color WITH ROLLUP --group by rollup(item,color)

order by Item,Color

Item Color TotalQuantity Stores

-------------------- ---------- --------------------------------------- -----------

NULL NULL 1272 13

Chair NULL 290 5

Chair Blue 123 2

Chair Red 167 3

Sofa NULL 2 1

Sofa Green 2 1

Table NULL 980 7

Table Blue 224 2

Table Green 533 2

Table Red 223 3

(10 行受影响)

select Item,Color,SUM(Quantity) as TotalQuantity,COUNT(Store) as Stores from Inventory group by  Item,Color WITH ROLLUP --group by rollup(item,color)order by Item,Color/*Item                 Color      TotalQuantity                           Stores-------------------- ---------- --------------------------------------- -----------NULL                 NULL       1272                                    13Chair                NULL       290                                     5Chair                Blue       123                                     2Chair                Red        167                                     3Sofa                 NULL       2                                       1Sofa                 Green      2                                       1Table                NULL       980                                     7Table                Blue       224                                     2Table                Green      533                                     2Table                Red        223                                     3(10 行受影响)*/

WITH CUBE 多维数据集，多维数据集的纬度取决于分组列的数目

select Item,Color,SUM(Quantity) as TotalQuantity,COUNT(Store) as Stores

from Inventory

group by Item,Color WITH cube --group by cube(item,color)

order by Item,Color

Item Color TotalQuantity Stores

-------------------- ---------- --------------------------------------- -----------

NULL NULL 1272 13

NULL Blue 347 4

NULL Green 535 3

NULL Red 390 6

Chair NULL 290 5

Chair Blue 123 2

Chair Red 167 3

Sofa NULL 2 1

Sofa Green 2 1

Table NULL 980 7

Table Blue 224 2

Table Green 533 2

Table Red 223 3

(13 行受影响)

select Item,Color,SUM(Quantity) as TotalQuantity,COUNT(Store) as Stores from Inventory group by  Item,Color WITH cube --group by cube(item,color)order by Item,Color/*Item                 Color      TotalQuantity                           Stores-------------------- ---------- --------------------------------------- -----------NULL                 NULL       1272                                    13NULL                 Blue       347                                     4NULL                 Green      535                                     3NULL                 Red        390                                     6Chair                NULL       290                                     5Chair                Blue       123                                     2Chair                Red        167                                     3Sofa                 NULL       2                                       1Sofa                 Green      2                                       1Table                NULL       980                                     7Table                Blue       224                                     2Table                Green      533                                     2Table                Red        223                                     3(13 行受影响)*/

GROUPING SETS(..) 仅返回最高级别

select Item,Color,SUM(Quantity) as TotalQuantity,COUNT(Store) as Stores

from Inventory

group by GROUPING sets(Item,Color)

order by Item,Color

Item Color TotalQuantity Stores

-------------------- ---------- --------------------------------------- -----------

NULL Blue 347 4

NULL Green 535 3

NULL Red 390 6

Chair NULL 290 5

Sofa NULL 2 1

Table NULL 980 7

(6 行受影响)

select Item,Color,SUM(Quantity) as TotalQuantity,COUNT(Store) as Stores from Inventory group by GROUPING sets(Item,Color)order by Item,Color/*Item                 Color      TotalQuantity                           Stores-------------------- ---------- --------------------------------------- -----------NULL                 Blue       347                                     4NULL                 Green      535                                     3NULL                 Red        390                                     6Chair                NULL       290                                     5Sofa                 NULL       2                                       1Table                NULL       980                                     7(6 行受影响)*/

混合使用：

返回store最高级别和cube的两个item，color纬度所以级别组合

select Item,Color,SUM(Quantity) as TotalQuantity,COUNT(Store) as Stores

from Inventory

group by GROUPING sets(Store) ,cube(Item,color)

order by Item,Color

Item Color TotalQuantity Stores

-------------------- ---------- --------------------------------------- -----------

NULL NULL 190 5

NULL NULL 504 5

NULL NULL 578 3

NULL Blue 225 2

NULL Blue 122 2

NULL Green 2 1

NULL Green 229 1

NULL Green 304 1

NULL Red 274 2

NULL Red 66 2

NULL Red 50 2

Chair NULL 32 2

Chair NULL 122 2

Chair NULL 136 1

Chair Blue 101 1

Chair Blue 22 1

Chair Red 10 1

Chair Red 21 1

Chair Red 136 1

Sofa NULL 2 1

Sofa Green 2 1

Table NULL 156 2

Table NULL 382 3

Table NULL 442 2

Table Blue 100 1

Table Blue 124 1

Table Green 229 1

Table Green 304 1

Table Red 29 1

Table Red 56 1

Table Red 138 1

(31 行受影响)

select Item,Color,SUM(Quantity) as TotalQuantity,COUNT(Store) as Stores from Inventory group by GROUPING sets(Store) ,cube(Item,color)order by Item,Color/*Item                 Color      TotalQuantity                           Stores-------------------- ---------- --------------------------------------- -----------NULL                 NULL       190                                     5NULL                 NULL       504                                     5NULL                 NULL       578                                     3NULL                 Blue       225                                     2NULL                 Blue       122                                     2NULL                 Green      2                                       1NULL                 Green      229                                     1NULL                 Green      304                                     1NULL                 Red        274                                     2NULL                 Red        66                                      2NULL                 Red        50                                      2Chair                NULL       32                                      2Chair                NULL       122                                     2Chair                NULL       136                                     1Chair                Blue       101                                     1Chair                Blue       22                                      1Chair                Red        10                                      1Chair                Red        21                                      1Chair                Red        136                                     1Sofa                 NULL       2                                       1Sofa                 Green      2                                       1Table                NULL       156                                     2Table                NULL       382                                     3Table                NULL       442                                     2Table                Blue       100                                     1Table                Blue       124                                     1Table                Green      229                                     1Table                Green      304                                     1Table                Red        29                                      1Table                Red        56                                      1Table                Red        138                                     1(31 行受影响)*/

转载请著名出处。 *博客地址：

转载于:https://www.cnblogs.com/blog-Aevin/p/4231090.html

你可能感兴趣的文章

Nagios图像绘制插件PNP4Nagios部署和测试

查看>>

在SqlServer2008R2中，在一张表上加上insert、update、delete触发器（带游标）

查看>>

常用模块--- 正则模块正则表达式 re 模块