SQL窗口函数与聚合函数应用-优快云博客

本文详细介绍了SQL中窗口函数(row_number, partitionby, rank, dense_rank)和聚合函数(sum)的应用，包括如何使用这些函数进行数据排序、分组求和及复杂查询。通过示例代码展示了如何在数据库查询中高效地处理数据。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

与over函数结合的几个函数

create table #tab(A varchar(8), B varchar(8))
insert into #tab
select 'A1', 'B1' union all
select 'A1', 'B2' union all
select 'A1', 'B3' union all
select 'A2', 'B4' union all
select 'A2', 'B5' union all
select 'A2', 'B6' union all
select 'A3', 'B7' union all
select 'A3', 'B8' union all
select 'A3', 'B9'

row_number() over() :

select row_number()over(order by A) as id ,* from #tab;

partition by:

按A分组，按A排序

select row_number()over(order by A) as id,
*,
row_number()over(partition by A order by A)Num
from #tab;

按A分组，组内按B倒序

select row_number()over(order by A) as id,
*,
row_number()over(partition by A order by B desc)Num
from #tab;

rank()over()

http://www.cnblogs.com/mycoding/archive/2010/05/29/1747065.html

select *,rank()over(order by A)from #tab;

select *,rank()over(partition by A order by A) from #tab;

select *,rank()over(partition by A order by B) from #tab;

也可以按多列分组，具体的看链接。

dense_rank()over()

select *,dense_rank()over(order by A)from #tab;

sum() over():

create table #tab(A varchar(8), B varchar(8),C int)
insert into #tab
select 'A1', 'B1' ,10 union all
select 'A1', 'B2' ,10union all
select 'A1', 'B3' ,10 union all
select 'A2', 'B4' ,20 union all
select 'A2', 'B5' ,20union all
select 'A2', 'B6' ,20union all
select 'A3', 'B7' ,30union all
select 'A3', 'B8',30 union all
select 'A3', 'B9',30

select *,sum(C)over(partition by A)from #tab;

select *,sum(C)over()from #tab;

http://hi.baidu.com/s__wind/item/a20107fa4eb6631ca6298858

http://www.cnblogs.com/85538649/archive/2011/08/13/2137370.html

http://www.cnblogs.com/lanzi/archive/2010/10/26/1861338.html

SQL Over