1. Group By 语句介绍:

Group By语句从英文字面意义上理解就是“根据(by)定规则进行分组(Group)”它作用是通过定规则将个数据集划分成若干个小区域然后针对若干个小区域进行数据处理

P.S. 这里真是体会到了个好命名力量Group By从字面是直接去理解是非常好理解恩以后在命名环节定要加把劲:)话题扯远了

2. Group By 使用:

上面已经给出了对Group By语句理解基于这个理解和SQL Server 2000联机帮助下面对Group By语句各种典型使用进行依次列举介绍说明

2.1 Group By [Expressions]:

这个恐怕是Group By语句最常见使用方法了Group By + [分组字段](可以有多个)在执行了这个操作以后数据集将根据分组字段值将个数据集划分成各个区别小组比如有如下数据集其中水果名称(FruitName)和出产国家(ProductPlace)为联合主键:



FruitName

ProductPlace

Price


Apple

China

$1.1


Apple

Japan

$2.1


Apple

USA

$2.5


Orange

China

$0.8


Banana

China

$3.1


Peach

USA

$3.0




如果我们想知道每个国家有多少种水果那么我们可以通过如下SQL语句来完成:

SELECTCOUNT(*)AS水果种类,ProductPlaceAS出产国
FROMT_TEST_FRUITINFO
GROUPBYProductPlace

这个SQL语句就是使用了Group By + 分组字段方式那么这句SQL语句就可以解释成“我按照出产国家(ProductPlace)将数据集进行分组然后分别按照各个组来统计各自记录数量”很好理解对吧这里值得注意是结果集中有两个返回字段个是ProductPlace(出产国), 个是水果种类如果我们这里水果种类不是用Count(*)而是类似如下写法话:

SELECTFruitName,ProductPlaceFROMT_TEST_FRUITINFOGROUPBYProductPlace

那么SQL在执行此语句时候会报如下类似:

选择列表中列'T_TEST_FRUITINFO.FruitName'无效该列没有包含在聚合或GROUPBY子句中

这就是我们需要注意点如果在返回集字段中这些字段要么就要包含在Group By语句后面作为分组依据；要么就要被包含在聚合中我们可以将Group By操作想象成如下个过程首先系统根据SELECT 语句得到个结果集如最开始那个水果、出产国家、单价个详细表然后根据分组字段将具有相同分组字段记录归并成了条记录这个时候剩下那些不存在于Group By语句后面作为分组依据字段就有可能出现多个值但是目前种分组情况只有条记录个数据格是无法放入多个数值所以这里就需要通过定处理将这些多值列转化成单值然后将其放在对应数据格中那么完成这个步骤就是聚合这就是为什么这些叫聚合(aggregate functions)了

2.2 Group By All [expressions] :

Group By All + 分组字段, 这个和前面提到Group By [Expressions]形式多了个关键字ALL这个关键字只有在使用了where语句且where条件筛选掉了些组情况才可以看出效果在SQL Server 2000联机帮助中对于Group By All是这样进行描述:

如果使用ALL关键字那么查询结果将包括由GROUPBY子句产生所有组即使某些组没有符合搜索条件行没有ALL关键字包含GROUPBY子句SELECT语句将不显示没有符合条件行组

其中有这么句话“如果使用ALL关键字那么查询结果将包含由Group By子句产生所有组...没有ALL关键字那么不显示不符合条件行组”这句话听起来好像挺耳熟对了好像和LEFT JOIN 和 RIGHT JOIN 有点像其实这里是类比LEFT JOIN来进行理解还是基于如下这样个数据集:

FruitName

ProductPlace

Price


Apple

China

$1.1


Apple

Japan

$2.1


Apple

USA

$2.5


Orange

China

$0.8


Banana

China

$3.1


Peach

USA

$3.0




首先我们不使用带ALL关键字Group By语句:

SELECTCOUNT(*)AS水果种类,ProductPlaceAS出产国
FROMT_TEST_FRUITINFO
WHERE(ProductPlace<>'Japan')
GROUPBYProductPlace

那么在最后结果中由于Japan不符合where语句所以分组结果中将不会出现Japan

现在我们加入ALL关键字:

SELECTCOUNT(*)AS水果种类,ProductPlaceAS出产国
FROMT_TEST_FRUITINFO
WHERE(ProductPlace<>'Japan')
GROUPBYALLProductPlace

重新运行后我们可以看到Japan分组但是对应“水果种类”不会进行真正统计聚合会根据返回值类型用默认值0或者NULL来代替聚合返回值

2.3 GROUP BY [Expressions] WITH CUBE | ROLLUP:

首先需要介绍说明是Group By All 语句是不能和CUBE 和 ROLLUP 关键字起使用

首先先说说CUBE关键字以下是SQL Server 2000联机帮助中介绍说明:

指定在结果集内不仅包含由GROUPBY提供正常行还包含汇总行在结果集内返回每个可能组和子组组合GROUPBY汇总行GROUPBY汇总行在结果中显示为NULL但可用来表示所有值使用GROUPING确定结果集内空值是否是GROUPBY汇总值

结果集内汇总行数取决于GROUPBY子句内包含列数GROUPBY子句中每个操作数(列)绑定在分组NULL下并且分组适用于所有其它操作数(列)由于CUBE返回每个可能组和子组组合因此不论指定分组列时所使用是什么顺序行数都相同


我们通常Group By语句是按照其后所跟所有字段进行分组而如果加入了CUBE关键字以后那么系统将根据所有字段进行分组基础上还会通过对所有这些分组字段所有可能存在组合形成分组条件进行分组计算由于上面举例子过于简单这里就再适合了现在我们数据集将换个场景个表中包含人员基本信息:员工所在部门编号(C_EMPLINFO_DEPTID)、员工性别(C_EMPLINFO_SEX)、员工姓名(C_EMPLINFO_NAME)等那么我现在想知道每个部门各个性别人数那么我们可以通过如下语句得到:

SELECTC_EMPLINFO_DEPTID,C_EMPLINFO_SEX,COUNT(*)ASC_EMPLINFO_TOTALSTAFFNUM
FROMT_PERSONNEL_EMPLINFO
GROUPBYC_EMPLINFO_DEPTID,C_EMPLINFO_SEX

但是如果我现在希望知道:

1. 所有部门有多少人(这里相当于就不进行分组了这里已经对员工部门和性别没有做任何限制了但是这确也是种分组条件组合方式)；

2. 每种性别有多人(这里实际上是仅仅根据性别(C_EMPLINFO_SEX)进行分组)；

3. 每个部门有多少人(这里仅仅是根据部门(C_EMPLINFO_DEPTID)进行分组)；那么我们就可以使用ROLLUP语句了

SELECTC_EMPLINFO_DEPTID,C_EMPLINFO_SEX,COUNT(*)ASC_EMPLINFO_TOTALSTAFFNUM
FROMT_PERSONNEL_EMPLINFO
GROUPBYC_EMPLINFO_DEPTID,C_EMPLINFO_SEXWITHCUBE

那么这里你可以看到结果集中多出了很多行而且结果集中某个字段或者多个字段、甚至全部字段都为NULL请仔细看下你就会发现实际上这些记录就是完成了上面我所列举所有统计数据展现使用过SQL Server 2005或者RDLC朋友们定对于矩阵小计和分组功能有印象吧是不是都可以通过这个得到答案我想RDLC中对于分组和小计计算就是通过Group ByCUBE和ROLLUP关键字来实现(个人意见未证实)

CUBE关键字还有个极为相似兄弟ROLLUP, 同样我们先从这英文入手ROLL UP是“向上卷”意思如果说CUBE组合是绝对自由那么ROLLUP组合就需要有点约束了我们先来看看SQL Server 2000联机中对ROLLUP关键字定义:

指定在结果集内不仅包含由GROUPBY提供正常行还包含汇总行按层次结构顺序从组内最低级别到最高级别汇总组组层次结构取决于指定分组列时所使用顺序更改分组列顺序会影响在结果集内生成行数

那么这个顺序是什么呢？对了就是Group By 后面字段顺序排在靠近Group By分组字段级别高然后是依次递减如:Group By Column1, Column2, Column3那么分组级别从高到低顺序是:Column1 > Column2 > Column3还是看我们前面例子SQL语句中我们仅仅将CUBE关键字替换成ROLLUP关键字如:

SELECTC_EMPLINFO_DEPTID,C_EMPLINFO_SEX,COUNT(*)ASC_EMPLINFO_TOTALSTAFFNUM
FROMT_PERSONNEL_EMPLINFO
GROUPBYC_EMPLINFO_DEPTID,C_EMPLINFO_SEXWITHROLLUP

和CUBE相比返回数据行数减少了不少:)仔细看下除了正常Group By语句后数据中还包含了:

1. 部门员工数；(向上卷了次这次先去掉了员工性别分组限制)

2. 所有部门员工数；(向上又卷了依次这次去掉了员工所在部门分组限制)

在现实应用中对于报表些统计功能是很有帮助

这里还有个问题需要补充介绍说明下如果我们使用ROLLUP或者CUBE关键字那么将产生些小计行这些行中被剔除在分组原因的外字段将会被设置为NULL,那么还存在种情况比如在作为分组依据列表中存在可空行那么NULL也会被作为个分组表示出来所以这里我们就不能仅仅通过NULL来判断是不是小计记录了下面例子展示了这里说得到情况还是我们前面提到水果例子现在我们在每种商品后面增加个“折扣列”(Discount)用于显示对应商品折扣这个数值是可空也就是可以通过NULL来表示没有对应折扣信息数据集如下所示:

FruitName

ProductPlace

Price

Discount


Apple

China

$1.1

0.8


Apple

Japan

$2.1

0.9


Apple

USA

$2.5

1.0


Orange

China

$0.8

NULL


Banana

China

$3.1

NULL


Peach

USA

$3.0

NULL




现在我们要统计“各种折扣对应有多少种商品并总计商品总数”那么我们可以通过如下SQL语句来完成:

SELECTCOUNT(*)ASProductCount,Discount
FROMT_TEST_FRUITINFO
GROUPBYDiscountWITHROLLUP

好了运行下你会发现数据都正常出来了按照如上数据集结果如下所示:

ProductCount

Discount


3

NULL


1

0.8


1

0.9


1

1.0


6

NULL




好了各种折扣商品数量都出来了但是在显示“没有折扣商品”和“商品小计”时候判断上确存在问题存在两条Discount为Null记录是哪条呢？通过分析数据我们知道第条数据(3, Null)应该对应没有折扣商品数量而(6,Null)应该对应所有商品数量需要判断这两个具有区别意义Null就需要引入个聚合Grouping现在我们把语句修改下在返回值中使用Grouping增加列返回值SQL语句如下:

SELECTCOUNT(*)ASProductCount,Discount,GROUPING(Discount)ASExpr1
FROMT_TEST_FRUITINFO
GROUPBYDiscountWITHROLLUP

这个时候我们再看看运行结果:

ProductCount

Discount

Expr1


3

NULL

0


1

0.8

0


1

0.9

0


1

1.0

0


6

NULL

1




对于根据指定字段Grouping中包含字段进行小计记录这里会标记为1我们就可以通过这个标记值将小计记录从判断那些由于ROLLUP或者CUBE关键字产生行Grouping(column_name)可以带个参数Grouping就会去判断对应字段值NULL是否是由ROLLUP或者CUBE产生特殊NULL值如果是那么就在由Grouping聚合产生新列中将值设置为1注意Grouping只会检查Column_name对应NULL来决定是否将值设置为1而不是完全由此列是否是由ROLLUP或者CUBE关键字自动添加来决定

2.2Group By 和 Having, Where ,Order by语句执行顺序:

最后要介绍说明下Group By, Having, Where, Order by几个语句执行顺序个SQL语句往往会产生多个临时视图那么这些关键字执行顺序就非常重要了你必须了解这个关键字是在对应视图形成前字段进行操作还是对形成临时视图进行操作这个问题在使用了别名视图尤其重要以上列举关键字是按照如下顺序进行执行:Where, Group By, Having, Order by首先where将最原始记录中不满足条件记录删除(所以应该在where语句中尽量将不符合条件记录筛选掉这样可以减少分组次数)然后通过Group By关键字后面指定分组条件将筛选得到视图进行分组接着系统根据Having关键字后面指定筛选条件将分组视图后不满足条件记录筛选掉然后按照Order By语句对视图进行排序这样最终结果就产生了在这 4个关键字中只有在Order By语句中才可以使用最终视图列名如:

SELECTFruitName,ProductPlace,Price,IDASIDE,Discount
FROMT_TEST_FRUITINFO
WHERE(ProductPlace=N'china')
ORDERBYIDE

这里只有在ORDER BY语句中才可以使用IDE其他条件语句中如果需要引用列名则只能使用ID而不能使用IDE