T_Employee
GROUP BY FDepartment
执行完毕我们就能在输出结果中看到下面的执行结果:
FDepartment FAgeMIN FAgeMAX
Development 25 28
HumanResource 23 25
InfoTech 27 28
Sales 22 28
SELECT FSubCompany,FAge,COUNT(*) AS CountOfThisSubCompAge FROM
T_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
HAVING 语句
有的时候需要对部分分组进行过滤,比如只检索人数多余1个的年龄段,有的开发人员会使用下面的SQL语句:
SELECT FAge,COUNT(*) AS CountOfThisAge FROM T_Employee
GROUP BY FAge
WHERE COUNT(*)>1
可以在数据库系统中执行下面的SQL的时候,数据库系统会提示语法错误,这是因为聚合函
数不能在WHERE语句中使用,必须使用HAVING子句来代替,比如:
SELECT FAge,COUNT(*) AS CountOfThisAge FROM T_Employee
GROUP BY FAge
HAVING COUNT(*)>1
执行完毕我们就能在输出结果中看到下面的执行结果:
FAge CountOfThisAge
23 2
25 2
28 3
HAVING语句中也可以像WHERE语句一样使用复杂的过滤条件,比如下面的SQL用来检索人
数为1个或者3个的年龄段,可以使用下面的SQL:
SELECT FAge,COUNT(*) AS CountOfThisAge FROM T_Employee
GROUP BY FAge
HAVING COUNT(*) =1 OR COUNT(*) =3
HAVING语句能够使用的语法和WHERE几乎是一样的,不过使用WHERE的时候
GROUP BY子句要位于WHERE子句之后,而使用HAVING子句的时候GROUP BY子句要位
于HAVING子句之后,比如下面的SQL是错误的:
SELECT FAge,COUNT(*) AS CountOfThisAge FROM T_Employee
HAVING COUNT(*) IN (1,3)
GROUP BY FAge
需要特别注意,在HAVING语句中不能包含未分组的列名,比如下面的SQL语句是错误
的:
SELECT FAge,COUNT(*) AS CountOfThisAge FROM T_Employee
GROUP BY FAge
HAVING FName IS NOT NULL
执行的时候数据库系统会提示类似如下的错误信息:
HAVING 子句中的列 'T_Employee.FName' 无效,因为该列没有包含在聚合函数或 GROUP BY 子句中。
需要用WHERE语句来代替HAVING,修改后的SQL语句如下:
SELECT FAge,COUNT(*) AS CountOfThisAge FROM T_Employee
WHERE FName IS NOT NULL
GROUP BY FAge
限制结果集行数 (窗口函数ROW_NUMBER())
如下:
ROW_NUMBER OVER(排序规则)
比如我们执行下面的SQL语句:
SELECT ROW_NUMBER() OVER(ORDER BY FSalary),FNumber,FName,FSalary,FAge
FROM T_Employee
FNumber FName FSalary FAge
1 DEV001 Tom 8300.00 25
2 SALES002 Kerry 6200.00 28
3 HR002 Tina 5200.36 25
4 SALES001 John 5000.00 23
5 IT001 Smith 3900.00 28
可以看到第一列中的数据就是通过ROW_NUMBER()计算出来的行号。
如下的方式来实现返回第3行到第5行的数据(按照工资降序):
SELECT ROW_NUMBER() OVER(ORDER BY FSalary
DESC),FNumber,FName,FSalary,FAge
FROM T_Employee
WHERE (ROW_NUMBER() OVER(ORDER BY FSalary DESC))>=3
AND (ROW_NUMBER() OVER(ORDER BY FSalary DESC))<=5
但是在运行的时候数据库系统会报出下面的错误信息:
开窗函数只能出现在 SELECT 或 ORDER BY 子句中。
也就是说 ROW_NUMBER()不能用在WHERE语句中 。我们可以用子查询来解决这个问题,下面
的SQL语句用来返回第3行到第5行的数据:
SELECT * FROM
(
SELECT ROW_NUMBER() OVER(ORDER BY FSalary DESC) AS rownum,
FNumber,FName,FSalary,FAge FROM T_Employee
) AS a
WHERE a.rownum>=3 AND a.rownum<=5
ps: Oracle中支持窗口函数ROW_NUMBER(),其用法和MSSQLServer2005中相同,比如我们
执行下面的SQL语句:
SELECT * FROM
(
SELECT ROW_NUMBER() OVER(ORDER BY FSalary DESC) row_num,
FNumber,FName,FSalary,FAge FROM T_Employee
) a
WHERE a.row_num>=3 AND a.row_num<=5