MSSQL OVER()函数简介及应用举例


一、over函数简介

over函数是MSSQL 2005中提供的一个系统函数
over函数是在返回结果中,增加一列,
此列由对结果集中所有行,进行运算而生成
over函数:over函数常常结合 partition by order by 及聚合函数得到相关的列


二、over函数应用

例:

 CREATE TABLE [dbo].[A](
[keyId1] [int],
[keyId2] [int],
) 
GO

--测试数据
insert into A (keyId1, keyId2) values (10, 8),(20, 8),(30, 8);
insert into A (keyId1, keyId2) values (40, 6),(50, 6),(60, 6);
insert into A (keyId1, keyId2) values (70, 10),(80, 10),(90, 10);
insert into A (keyId1, keyId2) values (100, 17),(111, 17),(121, 17);
go
  
  ----over() 代表对所有返回结果集
  ----over(PARTITION BY keyId2) ---根据keyId2 当前行所对应的值 获取结果集中 等于当前行的keyId2的行 进行相关计算
SELECT keyId1,keyId2, 
  COUNT(keyId1) OVER() AS countAll,                           --总keyId1纪录数
  MIN(keyId1) OVER()   AS minKeyId1,                            --最小keyId1号
  MAX(keyId1) OVER()   AS maxKeyId1,                            --最大keyId1号
  AVG(keyId1) OVER()   AS avgKeyId1,                            --keyId1平均
  SUM(keyId1) OVER()   AS sumid,                                --keyId1之和
  AVG(keyId1) OVER(PARTITION BY keyId2) AS avg_keyId2,        --按keyId2分区, 求keyId1平均
  SUM(keyId1) OVER(PARTITION BY keyId2) AS total_keyId2       --按keyId2分区, 求keyId1和
FROM A
order by keyId1;

SELECT keyId1, keyId2,
  row_number() over(order by keyId1 desc) AS [行编号],                               --与排序函数一起使用, 根据keyId1降序排序
  row_number() over(partition by keyId2 order by keyId1 desc) AS [群组中行编号]      --与排序函数一起使用, 按keyId2分组,然后根据keyId1降序排序,生成在群组中的顺序号
FROM A
ORDER BY keyId1;
go


truncate table A
drop     table A 
go