sql server中2名同级学生的积分计算

Question

Rank_Table

ID  Rank    
1   1   
2   1   
3   3   
4   3   
5   5   

价格

No  Points  
1   10  
2   9   
3   8   
4   7   
5   6   

预期产出

ID  Rank    Points  
1   1       9.5     
2   1       9.5     
3   3       7.5     
4   3       7.5     
5   5       6       

第二名不存在，因此第1点和第2点相加，分布在学生人数之间，如：(10+9) /2= 9.5

当我加入两个表时，就像

select *
from Rank_table a join
     Price b
     on a.ID = b.No

我得到的输出是

ID  Rank    No  Points  
1   1      1    10  
2   1      2    9   
3   3      3    8   
4   3      4    7   
5   5      5    6   

Answer 1

这似乎是一个非常简单的要求，只需使用AVG和一个OVER子句。

CREATE TABLE [Rank] (ID int, [Rank] int)
CREATE TABLE Price ([No] int, Points int);
GO

INSERT INTO [Rank]
VALUES
(1,1),   
(2,1),   
(3,3),   
(4,3),   
(5,5);   

INSERT INTO Price
VALUES
(1,10),  
(2,9),  
(3,8),  
(4,7),  
(5,6);
GO
SELECT R.ID,
       R.[Rank],
       AVG(CONVERT(decimal(2,0),P.Points)) OVER (PARTITION BY R.[Rank]) AS Points
FROM [Rank] R
     JOIN Price P ON R.ID = P.[No];

GO
DROP TABLE [Rank];
DROP TABLE Price;

Answer 2

您需要简单的JOIN：

select rn.*,
       avg(convert(decimal(10,0), p.Points)) over (partition by rn.rnk) as points
from Rank_Table rn inner join 
     Price p
     on p.id = rn.No;  

Answer 3

嗯。您似乎希望基于“下一个”级别以及每一行中的级别进行非等号连接。不幸的是，Server 2008不支持lead()，但您可以使用apply

select rt.id, rt.rank, avg(p.price * 1.0) as points
from rank_table rt outer apply
     (select min(rt2.rank) as next_rank
      from rank_table rt2
      where rt2.rank > rt.rank
     ) rt2 left join
     price p
     on p.no >= rt.rank >= p.no and
        (p.no < rt2.next_rank or rt2.next_rank is null)
group by rt.id, rt.rank;