同一个表里如何查出多个学生分数涨幅排序

[梅小西Echo]

一个表里存了学生id，日期，分数。就是每个学生每天的考试分数，大概存了十多天的成绩。
想要个需求，是查出最近分数涨幅最高的排序，比如学生A最近的日期是今天，那么就跟上一天比，注意这个上一天不一定简单的减1，有可能中间有周末。学生B最近的日期是昨天(假如他今天缺考)，那就是昨天的和前一天的比。
然后这个涨幅的百分比来个由高到低的排序。日期是int型

表sql

CREATE TABLE [dbo].[test] (
[sid] int NOT NULL ,
[date] int NULL ,
[score] int NULL 
)


GO

-- ----------------------------
-- Records of test
-- ----------------------------
INSERT INTO [dbo].[test] ([sid], [date], [score]) VALUES (N'1', N'20140526', N'67');
GO
INSERT INTO [dbo].[test] ([sid], [date], [score]) VALUES (N'1', N'20140525', N'77');
GO
INSERT INTO [dbo].[test] ([sid], [date], [score]) VALUES (N'1', N'20140524', N'23');
GO
INSERT INTO [dbo].[test] ([sid], [date], [score]) VALUES (N'2', N'20140525', N'44');
GO
INSERT INTO [dbo].[test] ([sid], [date], [score]) VALUES (N'2', N'20140524', N'66');
GO
INSERT INTO [dbo].[test] ([sid], [date], [score]) VALUES (N'2', N'20140523', N'89');
GO
INSERT INTO [dbo].[test] ([sid], [date], [score]) VALUES (N'3', N'20140526', N'55');
GO
INSERT INTO [dbo].[test] ([sid], [date], [score]) VALUES (N'3', N'20140525', N'55');
GO
INSERT INTO [dbo].[test] ([sid], [date], [score]) VALUES (N'3', N'20140524', N'34');
GO

[让你望见影子的墙]

;
with wang as (select rowid=ROW_NUMBER() over(PARTITION by sid order by date),* from test)

select *,t.score-s.score
from wang s join wang t on s.sid=t.sid and s.rowid=t.rowid+1
order by t.score-s.score desc

2	2	20140524	66	1	2	20140523	89	23
3	2	20140525	44	2	2	20140524	66	22
3	1	20140526	67	2	1	20140525	77	10
3	3	20140526	55	2	3	20140525	55	0
2	3	20140525	55	1	3	20140524	34	-21
2	1	20140525	77	1	1	20140524	23	-54

[werftom]

路过学习一下

[梅小西Echo]

引用 1 楼 HEROWANG 的回复:

;
with wang as (select rowid=ROW_NUMBER() over(PARTITION by sid order by date),* from test)

select *,t.score-s.score
from wang s join wang t on s.sid=t.sid and s.rowid=t.rowid+1
order by t.score-s.score desc

2	2	20140524	66	1	2	20140523	89	23
3	2	20140525	44	2	2	20140524	66	22
3	1	20140526	67	2	1	20140525	77	10
3	3	20140526	55	2	3	20140525	55	0
2	3	20140525	55	1	3	20140524	34	-21
2	1	20140525	77	1	1	20140524	23	-54

不对啊帅哥，有2个问题， 一是 你这颠倒了，应该是今天减昨天。第二，为什么结果会出现2个一样的学生id，按理说结果中只能出现唯一的学生id啊，出现2个就不合理了，你看你这个结果中第一和第二名都是2号了。

[梅小西Echo]

求助啊各位老大

[以学习为目的]

引用 3 楼 wang8118 的回复:

[quote=引用 1 楼 HEROWANG 的回复:]

;
with wang as (select rowid=ROW_NUMBER() over(PARTITION by sid order by date),* from test)

select *,t.score-s.score
from wang s join wang t on s.sid=t.sid and s.rowid=t.rowid+1
order by t.score-s.score desc

2	2	20140524	66	1	2	20140523	89	23
3	2	20140525	44	2	2	20140524	66	22
3	1	20140526	67	2	1	20140525	77	10
3	3	20140526	55	2	3	20140525	55	0
2	3	20140525	55	1	3	20140524	34	-21
2	1	20140525	77	1	1	20140524	23	-54

不对啊帅哥，有2个问题， 一是 你这颠倒了，应该是今天减昨天。第二，为什么结果会出现2个一样的学生id，按理说结果中只能出现唯一的学生id啊，出现2个就不合理了，你看你这个结果中第一和第二名都是2号了。[/quote] 兄弟，你这个其实是没问题啊。第一，你说的颠倒，你可以直接改下 ; with wang as (select rowid=ROW_NUMBER() over(PARTITION by sid order by date),* from test) select *,s.score-t.score from wang s join wang t on s.sid=t.sid and s.rowid=t.rowid+1 order by s.score-t.score desc 第二个问题，因为可能这个学校连续3次考试，每次的成绩增幅都比较大，所以增幅就排第一第二了也没问题啊

[以学习为目的]

2	1	20140525	77	1	1	20140524	23	54
2	3	20140525	55	1	3	20140524	34	21
3	3	20140526	55	2	3	20140525	55	0
3	1	20140526	67	2	1	20140525	77	-10
3	2	20140525	44	2	2	20140524	66	-22
2	2	20140524	66	1	2	20140523	89	-23

改过之后的结果，可以看出，同一个学生sid=2的，24号减23号的，25号减24号的都是负增长。而且排序也在一起

[shinger126]

with t as (select ROW_NUMBER() over (PARTITION by sid order by date desc) xh,* from test) select t.score-a.score ,t.* from t,t a where t.sid=a.sid and a.xh=t.xh+1 and t.xh=1 order by t.score-a.score desc