关于SQL Server分组取前N条的问题

最新推荐文章于 2023-06-27 08:47:41 发布

原创最新推荐文章于 2023-06-27 08:47:41 发布 · 970 阅读

0 ·

本内容遵循CC 4.0 BY-SA版权协议

本文介绍了一种通过去除一定比例的最高分和最低分来计算参赛者平均得分的方法。使用了临时表存储分数详情，并通过两次更新操作标记极值记录。最终通过查询未被标记的记录来计算每位参赛者的调整后平均得分。

昨天遇到这样一个问题，有两张表，结构如下

分数表

分数表主键分数详细表主键选手主键

分数详细表

分数详细表主键分数

要求计算去掉N% 条最高分和N%条最低分后选手的得分。

我的思路是这样的

1、创建一张临时表，包含（分数详细表主键，分数，选手主键，标记位）。注：标记位默认值为零。

2、更新临时表，以[选手主键]分组排序，将将每组前后N%条记录的[标记位]更新为1。

3、将临时表中标志位为0的纪录进行分组统计，求出平均数，得到结果

测试数据

--创建测试环境
Create Table 分数表
(
分数表主键 Int,
分数详细表主键 Int,
选手主键 int
)
Insert Into 分数表 (分数表主键,分数详细表主键,选手主键)
Select 1,1,1
Union Select 2,2,2
Union Select 3,3,1
Union Select 4,4,2
Union Select 5,5,1
Union Select 6,6,2
Union Select 7,7,1
Union Select 8,8,2
Union Select 9,9,1
Union Select 10,10,2
Union Select 11,11,1
Union Select 12,12,2
Union Select 13,13,1
Union Select 14,14,2

Create Table 分数详细表
(
分数详细表主键 Int,
分数 Decimal
)
Insert Into 分数详细表(分数详细表主键,分数)
Select 1,98
Union Select 2,94
Union Select 3,84
Union Select 4,89
Union Select 5,78
Union Select 6,95
Union Select 7,96
Union Select 8,100
Union Select 9,99
Union Select 10,45
Union Select 11,65
Union Select 12,87
Union Select 13,68
Union Select 14,85

--创建临时表
Select A.分数详细表主键,分数,选手主键,0 as 标志位 Into #
From 分数详细表 A Inner Join 分数表 B On A.分数详细表主键 = B.分数详细表主键
Select * FRom # Order By 选手主键,分数
--更新临时表，假设N值为10。
Update # Set 标志位 = 1 From # A
Where 分数详细表主键 In (Select Top 10 Percent 分数详细表主键 From # B Where A.选手主键 = B.选手主键 Order By 分数)
Update # Set 标志位 = 1 From # A
Where 分数详细表主键 In (Select Top 10 Percent 分数详细表主键 From # B Where A.选手主键 = B.选手主键 Order By 分数 Desc)
Select * FRom # Order By 选手主键,分数
--计算平均值，得到结果
Select 选手主键,AVG(分数) As 分数
From # Where 标志位 = 0
Group By 选手主键

Drop Table 分数表
Drop Table 分数详细表

Drop table #

标签