葵花宝典之SQL篇(2)

第三招：数据分组

11、数据分组入门

SQL语句中使用GROUP BY子句进行分组，使用方式为“GROUP BY 分组字段”

1SELECT FAge FROM T_Employee

GROUP BY FAge

2SELECT FAge,FSalary FROM T_Employee

GROUP BY FAge 这句是错误的！

SELECT FAge,AVG(FSalary) FROM T_Employee

GROUP BY FAge 这句是正确的！

3SELECT FSubCompany,FDepartment FROM T_Employee

GROUP BY FSubCompany,FDepartment “组中组的实现”

4查看每个年龄段的员工的人数

SELECT FAge,COUNT(*) AS CountOfThisAge FROM T_Employee

GROUP BY FAge

结果：22 1

23 2

25 2

27 1

28 3

5用多个分组来实现更精细的数据统计

SELECT FSubCompany,FAge,COUNT(*) AS CountOfThisSubCompAge FROMT_Employee

GROUP BY FSubCompany,FAge

结果：FSubCompany FAge CountOfThisAge

ShenZhen 22 1

Beijing 23 2

Beijing 25 2

ShenZhen 27 1

Beijing 28 2

ShenZhen 28 1

6为了更容易的按照每个分公司进行查看，我们可以使用 ORDER BY

SELECT FSubCompany,FAge,COUNT(*) AS CountOfThisSubCompAge FROMT_Employee

GROUP BY FSubCompany,FAge

ORDER BY FSubCompany

结果：

FSubCompany FAge CountOfThisSubCompAge

Beijing 23 2

Beijing 25 2

Beijing 28 2

ShenZhen 22 1

ShenZhen 27 1

ShenZhen 28 1

7统计每个公司中的工资的总值：

SELECT FSubCompany,SUM(FSalary) AS FSalarySUM FROM T_Employee

GROUP BY FSubCompany

结果：

FSubCompany FSalarySUM

Beijing 30801.24

ShenZhen 6300.80

8以统计每个垂直部门中的工资的平均值

SELECT FDepartment,SUM(FSalary) AS FSalarySUM FROM T_Employee

GROUP BY FDepartment

结果：

FDepartment FSalarySUM

Development 10600.80

HumanResource 7401.24

InfoTech 6700.00

Sales 12400.00

9统计每个垂直部门中员工年龄的最大值和最小值

SELECT FDepartment,MIN(FAge) AS FAgeMIN,MAX(FAge) AS FAgeMAX FROMT_EmployeeGROUP BY FDepartment

结果：FDepartment FAgeMIN FAgeMAX

Development 25 28

HumanResource 23 25

InfoTech 27 28

Sales 22 28

（2009-7-3 23：50 今天就到这了，好累，睡觉了）

12、HAVING 语句

聚合函数不能在WHERE中使用，必须用HAVING来代替

如：下面语句会报错

SELECT FAge,COUNT(*) AS CountOfThisAge FROM T_Employee

GROUP BY FAge

WHERE COUNT(*)>1

正确的应该这样写：

SELECT FAge,COUNT(*) AS CountOfThisAge FROM T_Employee

GROUP BY FAge

HAVING COUNT(*)>1

★HAVING语句中也可以像WHERE语句一样使用复杂的过滤条件，比如下面的SQL用来检索人数为1个或者3个的年龄段，可以使用下面的SQL：

SELECT FAge,COUNT(*) AS CountOfThisAge FROM T_Employee

GROUP BY FAge

HAVING COUNT(*) =1 OR COUNT(*) =3

结果：

FAge CountOfThisAge

22 1

27 1

28 3

13、限制结果集行数

检索从第六名开始的一共三个人的信息（MSSQL2000）

SELECT top 3 * FROM T_Employee

WHERE FNumber NOT IN

(SELECT TOP 5 FNumber FROM T_Employee ORDER BY FSalary DESC)

ORDER BY FSalary DESC

14、数据库分页

15、抑制数据重复

DISTINCT关键字是用来进行重复数据抑制的最

简单的功能，而且所有的数据库系统都支持DISTINCT，DISTINCT的使用也非常简单，只要在SELECT之后增加DISTINCT即可

SELECT DISTINCT FDepartment FROM T_Employee

☆ DISTINCT是对整个结果集进行数据重复抑制的，而不是针对每一个列

例如：SELECT DISTINCT FDepartment,FSubCompany FROM T_Employee

得到的结果是：

FDepartment FSubCompany

Development Beijing

Development ShenZhen

HumanResource Beijing

InfoTech Beijing

InfoTech ShenZhen

Sales Beijing

Sales ShenZhen

很显然，FDepartment有重复的，但是整个结果集是没有重复的

16、计算字段

例如：SELECT FNumber,FName,FAge * FSalary FROM T_Employee

FAge * FSalary并不是原来就有的，而是计算出来的