SQL 聚合函数-非聚合函数

聚合函数：聚合函数就是对一组值进行计算后返回单个值（即分组）。聚合函数在计算时都会忽略空值(null)。
所有的聚合函数均为确定性函数。即任何时候使用一组相同的输入值调用聚合函数执行后的返回值都是相同的，无二义性。

2 聚合开窗函数

聚合函数加上 over() 开窗函数就是聚合开窗函数。

 

CREATE TABLE S_tudent(
S_Id INT PRIMARY KEY NOT NULL
,S_StuNo NVARCHAR(10)
,S_Name NVARCHAR(30)
,S_Sex CHAR(2)
,S_Height NVARCHAR(10)
,S_BirthDate NVARCHAR(30)
,C_S_Id INT 
)
SELECT * FROM dbo.S_tudent;
INSERT INTO dbo.S_tudent( S_Id ,S_StuNo ,S_Name ,S_Sex ,S_Height ,S_BirthDate ,C_S_Id)
VALUES(1,'001','项目','男','190','2017-01-01',1);

INSERT INTO dbo.S_tudent( S_Id ,S_StuNo ,S_Name ,S_Sex ,S_Height ,S_BirthDate ,C_S_Id)
VALUES(6,'002','刘邦','男','190','2017-01-01',NULL);

INSERT INTO dbo.S_tudent( S_Id ,S_StuNo ,S_Name ,S_Sex ,S_Height ,S_BirthDate ,C_S_Id)
VALUES(7,'003','貂蝉','女','180','2017-01-01',NULL);

INSERT INTO dbo.S_tudent( S_Id ,S_StuNo ,S_Name ,S_Sex ,S_Height ,S_BirthDate ,C_S_Id)
VALUES(9,'005','少司命','女','175','2017-01-01',4);

INSERT INTO dbo.S_tudent( S_Id ,S_StuNo ,S_Name ,S_Sex ,S_Height ,S_BirthDate ,C_S_Id)
VALUES(13,'009','天明','男','155','2017-01-01',4);

INSERT INTO dbo.S_tudent( S_Id ,S_StuNo ,S_Name ,S_Sex ,S_Height ,S_BirthDate ,C_S_Id)
VALUES(16,'012','盖聂','男','185','2017-01-01',1);

INSERT INTO dbo.S_tudent( S_Id ,S_StuNo ,S_Name ,S_Sex ,S_Height ,S_BirthDate ,C_S_Id)
VALUES(17,'013','卫庄','男','185','2017-01-01',1);

INSERT INTO dbo.S_tudent( S_Id ,S_StuNo ,S_Name ,S_Sex ,S_Height ,S_BirthDate ,C_S_Id)
VALUES(18,'014','大司命','女','183','2017-01-01',2);

INSERT INTO dbo.S_tudent( S_Id ,S_StuNo ,S_Name ,S_Sex ,S_Height ,S_BirthDate ,C_S_Id)
VALUES(19,'015','月儿','女','165','2017-01-01',2);