金典 SQL札记(9)

page301-354其他解决方案---开窗函数--测试数据及表USE [NB]GO/****** 对象:  Table [dbo].[T_Person2]    脚本日期: 08/14/2015 11:24:46 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOSET ANSI_PADDING ONGOCREATE TABLE [dbo].[T_Person3] (      [FName] [varchar] (20) NULL,      [FCity] [varchar] (20) NULL,      [FAge] INT,      FSalary INT) ON [PRIMARY]GOSET ANSI_PADDING OFFINSERT INTO T_Person3(FName ,FCity, FAge,FSalary )VALUES('Tom' ,'BeiJing', 20,3000 )INSERT INTO T_Person3(FName ,FCity, FAge,FSalary )VALUES('Tim' ,'ChengDu', 21,4000 )INSERT INTO T_Person3(FName ,FCity, FAge,FSalary )VALUES('Jim' ,'BeiJing', 22,3500 )INSERT INTO T_Person3(FName ,FCity, FAge,FSalary )VALUES('Lily' ,'LonDon', 21,2000 )INSERT INTO T_Person3(FName ,FCity, FAge,FSalary )VALUES('John' ,'NewYork', 22,1000 )INSERT INTO T_Person3(FName ,FCity, FAge,FSalary )VALUES('YaoMing' ,'BeiJing', 20,3000 )INSERT INTO T_Person3(FName ,FCity, FAge,FSalary )VALUES('Swing' ,'LonDon', 22,2000 )INSERT INTO T_Person3(FName ,FCity, FAge,FSalary )VALUES('Guo' ,'NewYork', 20,2800 )INSERT INTO T_Person3(FName ,FCity, FAge,FSalary )VALUES('YuQian' ,'BeiJing', 24,8000 )INSERT INTO T_Person3(FName ,FCity, FAge,FSalary )VALUES('Ketty' ,'London', 25,8500 )INSERT INTO T_Person3(FName ,FCity, FAge,FSalary )VALUES('Merry' ,'BeiJing', 23,3500 )INSERT INTO T_Person3(FName ,FCity, FAge,FSalary )VALUES('Smith' ,'ChengDu', 30,3000 )INSERT INTO T_Person3(FName ,FCity, FAge,FSalary )VALUES('Bill' ,'BeiJing', 25,2000 )INSERT INTO T_Person3(FName ,FCity, FAge,FSalary )VALUES('Jerry' ,'NewYory', 24,3300 )SELECT * FROM T_Person3--与聚合函数一样,开窗函数也是对行集组进行聚合计算--但是它不像普通聚合函数那样每组只返回一个值 ,开窗函数可以--为每组返回多个值,因为开窗函数所执行聚合计算的行集组是窗--口ISO SQL 规定了这样的函数为开窗函数 ,在oracle 中则被称为--分析函数, 而在DB2中则被称为 OLAP函数SELECT COUNT (*) FROM T_Person3--下面俩个表达是一个意思SELECT FCity, FAge,COUNT (*) OVER () FROM T_Person3 SELECT FCity, FAge,COUNT (*) FROM T_Person3WHERE FSalary < 5000GROUP BY FCity,FAge--OVER关键字表示把函数当成开窗函数而不是聚合函数--SQL标准允许将所有聚合函数用做开窗函数 ,使用OVER 关键字来--区分这俩种用法--上面中count(*)over() 对于查询结果的每一行都返回所有符合--条件的行的条数OVER()关键字后的括号中还经常添加选项 ,用以--改变进行聚合运算的窗口范围如果 OVER()关键字后面的括号中的--选项为空, 则开窗函数会对结果集中的所有行进行聚合运算--partition by 子句--开窗函数over() 关键字后的括号中 ,可以使用partition by 子句--来定义行的区分,从而进行聚合计算与 group by子句不同,--partition by子句创建的分区是独立于结果集的 ,创建的分区只是--提供聚合计算的而且不同的开窗函数所创建的分区也互不影响--范例下面SQL 用于显示每一个人员的信息及所属城市的人员数--同一个SQL 语句中可以使用多个开窗函数 ,而且这些开窗函数并不会--相互干扰--范例SELECT FName, FCity, FAge , FSalary,COUNT(*) OVER ( PARTITION BY FCity),COUNT(*) OVER ( PARTITION BY FAge)  FROM t_Person3--with子句与子查询--一次定义多次使用用于提取子查询WITHSSSS AS(       SELECT FAge FROM T_person3 WHERE FAge <24)SELECT * FROM T_person3 AS t WHERE T.FAge IN ( SELECT * FROM SSSS )--374withcr as (     select FAge from T_person3 ) select * from T_person3 where FAge in ( select * from cr )