先看下面的一个表格，我们从左边的两列信息来统计出右边的结果。
在SQLServer中我们可以用over子句中来代替子查询实现来提高效率,over子句除了排名函数之外也可以和聚合函数配合。实现代码如下：
use
tempdb
go
if
(object_id
('tb'
) is
not
null
)
drop
table
tb
go
create
table
tb
(name
varchar
(10
),
val
int
)
go
insert
into
tb
select
'aa'
,
10
union
all
select
'aa'
,
20
union
all
select
'aa'
,
20
union
all
select
'aa'
,
30
union
all
select
'bb'
,
55
union
all
select
'bb'
,
45
union
all
select
'bb'
,
0
select
*
,
排名
=
rank
()over
(partition
by
name
order
by
val
)
,
占比
=
cast
(val
*
1.0
/
sum
(val
)over
(partition
by
name
) as
decimal
(2
,
2
))
,
距最大
=
val
-
max
(val
)over
(partition
by
name
)
,
距最小
=
val
-
min
(val
)over
(partition
by
name
)
,
距平均
=
val
-
avg
(val
)over
(partition
by
name
)
from
tb