这样看看是不是清楚了一点呢?表结构如下 name score num times a 90 5 1 b 80 4 1 c 30 3 1 d 60 2 1 e 70 1 1 a 80 5 2 b 70 4 2 c 20 3 2 d 60 2 2 e 50 1 2 a 70 5 3 b 70 7 3 c 30 8 3 d 70 9 3 e 70 10 3需要得到这样的结果 name score num desc times a 90 5 1 1 b 80 4 2 1 c 30 3 5 1 d 60 2 4 1 e 70 1 3 1 a 80 5 1 2 b 70 4 2 2 c 20 3 5 2 d 60 2 3 2 e 50 1 4 2 a 70 5 4 3 b 70 7 3 3 c 30 8 5 3 d 70 9 2 3 e 70 10 1 3注:意思是每个a,b,c,d,e根据score desc,num desc来排列!
select [name], score, num, (select count(*) from table1 where times = A.times and (score > A.score or score = A.score and num >= A.num) [desc], times from table1 A
name score num times
a 90 5 1
b 80 4 1
c 30 3 1
d 60 2 1
e 70 1 1
a 80 5 2
b 70 4 2
c 20 3 2
d 60 2 2
e 50 1 2
a 70 5 3
b 70 7 3
c 30 8 3
d 70 9 3
e 70 10 3需要得到这样的结果
name score num desc times
a 90 5 1 1
b 80 4 2 1
c 30 3 5 1
d 60 2 4 1
e 70 1 3 1
a 80 5 1 2
b 70 4 2 2
c 20 3 5 2
d 60 2 3 2
e 50 1 4 2
a 70 5 4 3
b 70 7 3 3
c 30 8 5 3
d 70 9 2 3
e 70 10 1 3注:意思是每个a,b,c,d,e根据score desc,num desc来排列!
(select count(*) from table1 where times = A.times
and (score > A.score or score = A.score and num >= A.num) [desc],
times from table1 A