金典 SQL笔记(9)

page301-354其他解决计划


---开窗函数
--测试数据及表
USE [NB]
GO
/****** 对象:  Table [dbo].[T_Person2]    脚本日期: 08/14/2015 11:24:46 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[T_Person3] (
      [FName] [varchar] (20) NULL,
      [FCity] [varchar] (20) NULL,
      [FAge] INT,
      FSalary INT
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

INSERT 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_Person3
WHERE FSalary < 5000
GROUP 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子句与子查询
--一次定义屡次应用用于提取子查询
WITH
SSSS AS
(
       SELECT FAge FROM T_person3 WHERE FAge <24
)

SELECT * FROM T_person3 AS t WHERE T.FAge IN ( SELECT * FROM SSSS )
--374


with
cr as
 (
     select FAge from T_person3
 )

 select * from T_person3 where FAge in ( select * from cr )
page301-354其他解决计划


---开窗函数
--测试数据及表
USE [NB]
G