假设分数的字段是score则。 select 名次=(select count(*) from test t1 where t1.score<=t2.score) ,score from test t2 order by 名次
这要看是什么DBMS, 方法不同.如果是MS SQL, 如果用临时表, 可以这样:select IDENTITY(int, 1,1) AS 名次, 学号, 成绩 into #table1 from table order by 成绩 descupdate #table1 set 名次=b.名次 from #table1 inner join (select 成绩, min(名次) as 名次 from #table group by 成绩) as a on #table1.成绩 = a.成绩
其实名次等于分数比该纪录分数高的纪录总数+1,所以可以这么写: SELECT NAME, SCORE, (SELECT COUNT(*)+1 FROM SCORE Y WHERE Y.SCORE>X.SCORE) FROM SCORE X ORDER BY SCORE
小问题： 若有两个并列第二的，下一名次是从3开始算，还是从4开始算？ 若从4 开始算，这样:(smartdonkey(聪明的毛驴)语句有点小问题，要改一下） select 名次=(select count(*)+1 from tablename t1 where t1.score>t2.score) ,score from tablename t2 order by 名次若是从3开始算： select 名次=(select count(distinct score)+1 from tablename t1 where t1.score>t2.score) ,score from tablename t2 order by 名次
ORACLE PL/SQL格式(ORACLE不允许SELECT中带RANK=(SELECT..)的格式): SELECT X.NAME, X.SCORE, SUM(DECODE(SIGN(Y.SCORE-X.SCORE),1,1,0))+1 RANK --计算Y.SCORE-X.SCORE>0的和 FROM SCORE X, SCORE Y GROUP BY X.NAME, X.SCORE ORDER BY X.SCORE DESC但是纪录太多效率会成问题
你们在什么数据库下测试过的？我用ORACLE说 错误位于第1行： ORA-00923: 未找到预期 FROM 关键字
To milchcow(奶牛): 刚才的测试结果是在公司里做的，表有记录578条，运行的结果相信你也看到了，耗时7.76秒，而且得到了全部结果。 现在在家里的PC上作一下测试，结果是5.56秒。我机器的配置：AMD DURON850, 394MB RAM, ORACLE 8.16。不明白你的测试结果为什么会有这么大的差异。当然这绝不是一种好办法，因为查找的纪录太多。我试了一下，当纪录数达到两千的时候，基本上就无法忍受了。所以我建立了一个FUNCTION，用来返回当前的RANK。CREATE OR REPLACE Function Get_RANK(p_score number) RETURN number IS w_rank number(6); BEGIN select count(*) into w_rank from score where score > p_score; return w_rank+1; EXCEPTION WHEN no_data_found THEN return 1 ; END Get_RANK; /然后用 SELECT x.NAME, x.SCORE, get_rank(x.score) rank FROM SCORE x ORDER BY SCORE DESC 来查询，2000千条纪录耗时4.53秒。如果按你说的，1条第1名，2条第2名，后面还是第3名的话，要改一下FUNCTION CREATE OR REPLACE Function Get_RANK(p_score number) RETURN number IS w_rank number(6); BEGIN select count(distinct score) into w_rank --用distinct过滤掉所有重复值 from score where score > p_score; return w_rank+1; EXCEPTION WHEN no_data_found THEN return 1 ; END Get_RANK; /查询结果耗时7.1秒
我这里慢的原因找出来了,因为我的成绩是从一个视图里取出来的.所以慢 我用你说的:SELECT NAME, SCORE, (SELECT COUNT(*)+1 FROM SCORE Y WHERE Y.SCORE>X.SCORE) FROM SCORE X ORDER BY SCOR 方法在SQL-SERVER里试,不到3秒钟就出来了,可是在ORACL里却出错.
没有考虑用Oracle的Rownum吗?在大数量的情况下, 看看下面的速度如何? (我没装Oracle, 可能不对)select b.名次, a.学号, a.成绩 from table a, (select 成绩, min(名次) 名次 from (select rownum 名次, 学号, 成绩 from table order by 成绩 desc)) b where a.成绩=b.成绩
To 四非兄： 你的想法比我的好，只是在子查询里用ORDER BY也要ORACLE 8.16的版本才支持，低的版本也不能生成带ORDER BY的VIEW。
哦 Oracle, 我的不懂. 呵呵
对了, 我写的语句还少了 "group by 成绩". 呵呵
ORACLE8.05版本太低,真不好用,但是一下又不能升上去,好难过.
nononono的方法效率应该最好，换成Sql Server 应该是：select b.名次, a.学号, a.成绩 from table a, (select 成绩, min(名次) 名次 from (select IDENTITY(int, 1,1) AS 名次, 学号, 成绩 from table group by 成绩 order by 成绩 desc)) b where a.成绩=b.成绩
select 名次=(select count(*) from test t1 where t1.score<=t2.score) ,score from test t2 order by 名次
set 名次=b.名次
from #table1 inner join (select 成绩, min(名次) as 名次 from #table group by 成绩) as a
on #table1.成绩 = a.成绩
SELECT NAME, SCORE, (SELECT COUNT(*)+1 FROM SCORE Y WHERE Y.SCORE>X.SCORE)
FROM SCORE X
ORDER BY SCORE
若有两个并列第二的，下一名次是从3开始算，还是从4开始算？
若从4 开始算，这样:(smartdonkey(聪明的毛驴)语句有点小问题，要改一下）
select 名次=(select count(*)+1 from tablename t1 where t1.score>t2.score) ,score from tablename t2
order by 名次若是从3开始算：
select 名次=(select count(distinct score)+1 from tablename t1 where t1.score>t2.score) ,score from tablename t2
order by 名次
SELECT X.NAME, X.SCORE,
SUM(DECODE(SIGN(Y.SCORE-X.SCORE),1,1,0))+1 RANK --计算Y.SCORE-X.SCORE>0的和
FROM SCORE X, SCORE Y
GROUP BY X.NAME, X.SCORE
ORDER BY X.SCORE DESC但是纪录太多效率会成问题
错误位于第1行：
ORA-00923: 未找到预期 FROM 关键字
第二中写法在ORACLE下测试通过
你再试一下
Oracle7 Server Release 7.1.5.2.3 - Production Release
With the distributed option
PL/SQL Release 2.1.5.2.0 - ProductionSQL> set pages 9999
SQL> desc score
Name Null? Type
------------------------------- -------- ----
NAME NOT NULL VARCHAR2(10)
SCORE NUMBER(6,2)SQL> select count(*) from score; COUNT(*)
----------
578SQL> set timing on
SQL> SELECT X.NAME, X.SCORE, SUM(DECODE(SIGN(Y.SCORE-X.SCORE),1,1,0))+1 RANK
2 FROM SCORE X, SCORE Y
3 GROUP BY X.NAME, X.SCORE
4 ORDER BY X.SCORE DESC;NAME SCORE RANK
---------- ---------- ----------
N570 285 1
N571 285 1
N568 284 3
N569 284 3
N566 283 5
N567 283 5
N564 282 7
N565 282 7
N562 281 9
N563 281 9
N560 280 11
N561 280 11
N558 279 13
N559 279 13
N556 278 15
N557 278 15
N554 277 17
N555 277 17
N552 276 19
N553 276 19
N550 275 21
N551 275 21
N548 274 23
N549 274 23
N546 273 25
N547 273 25
N544 272 27
N545 272 27
N542 271 29
N543 271 29
N540 270 31
N541 270 31
N538 269 33
N539 269 33
N536 268 35
N537 268 35
N534 267 37
N535 267 37
N532 266 39
N533 266 39
....
....
N303 151 269
N300 150 271
N301 150 271
N298 149 273
N299 149 273
N296 148 275
N297 148 275
N294 147 277
N295 147 277
N292 146 279
N293 146 279
N290 145 281
N291 145 281
N288 144 283
N289 144 283
N286 143 285
N287 143 285
N284 142 287
N285 142 287
N282 141 289
N283 141 289
N280 140 291
N281 140 291
N278 139 293
N279 139 293
N276 138 295
N277 138 295
N274 137 297
N275 137 297
N272 136 299
N273 136 299
N270 135 301
N271 135 301
N268 134 303
N269 134 303
N266 133 305
N267 133 305
N264 132 307
N265 132 307
N262 131 309
N263 131 309
N260 130 311
N261 130 311
N258 129 313
N259 129 313
N256 128 315
N257 128 315
N254 127 317
N255 127 317
N252 126 319
N253 126 319
N250 125 321
N251 125 321
N248 124 323
N249 124 323
N246 123 325
N247 123 325
N244 122 327
N245 122 327
N242 121 329
N243 121 329
N240 120 331
N241 120 331
N238 119 333
N239 119 333
N236 118 335
N237 118 335
N234 117 337
N235 117 337
N232 116 339
N233 116 339
N230 115 341
N231 115 341
N228 114 343
N229 114 343
N226 113 345
N227 113 345
N224 112 347
N225 112 347
N222 111 349
N223 111 349
N220 110 351
N221 110 351
N218 109 353
N219 109 353
N216 108 355
N217 108 355
N214 107 357
N215 107 357
N212 106 359
N213 106 359
N210 105 361
N211 105 361
N208 104 363
N209 104 363
N206 103 365
N207 103 365
N204 102 367
N205 102 367
N202 101 369
N203 101 369
A 100 371
N200 100 371
N201 100 371
N198 99 374
N199 99 374
B 98 376
C 98 376
N197 98 376
N196 98 376
N194 97 380
N195 97 380
N192 96 382
N193 96 382
D 95 384
E 95 384
F 95 384
N190 95 384
N191 95 384
N188 94 389
N189 94 389
N186 93 391
N187 93 391
N184 92 393
N185 92 393
N182 91 395
N183 91 395
G 90 397
N180 90 397
N181 90 397
N178 89 400
N179 89 400
N176 88 402
N177 88 402
N174 87 404
N175 87 404
N172 86 406
N173 86 406
N170 85 408
N171 85 408
N168 84 410
N169 84 410
N166 83 412
N167 83 412
N164 82 414
N165 82 414
N162 81 416
N163 81 416
N160 80 418
N161 80 418
N158 79 420
N159 79 420
N156 78 422
N157 78 422
N154 77 424
N155 77 424
N152 76 426
N153 76 426
N150 75 428
N151 75 428
N148 74 430
N149 74 430
N146 73 432
N147 73 432
N144 72 434
N145 72 434
N142 71 436
N143 71 436
N140 70 438
N141 70 438
N138 69 440
N139 69 440
N136 68 442
N137 68 442
N134 67 444
N135 67 444
N132 66 446
N133 66 446
N130 65 448
N131 65 448
N128 64 450
N129 64 450
N126 63 452
N127 63 452
N124 62 454
N125 62 454
N122 61 456
N123 61 456
N120 60 458
N121 60 458
N118 59 460
N119 59 460
N116 58 462
N117 58 462
N114 57 464
N115 57 464
N112 56 466
N113 56 466
N110 55 468
N111 55 468
N108 54 470
N109 54 470
N106 53 472
N107 53 472
N104 52 474
N105 52 474
N102 51 476
N103 51 476
N100 50 478
N101 50 478
N98 49 480
N99 49 480
N96 48 482
N97 48 482
N94 47 484
N95 47 484
N92 46 486
N93 46 486
N90 45 488
N91 45 488
N88 44 490
N89 44 490
N86 43 492
N87 43 492
N84 42 494
N85 42 494
N82 41 496
N83 41 496
N80 40 498
N81 40 498
N78 39 500
N79 39 500
N76 38 502
N77 38 502
N74 37 504
N75 37 504
N72 36 506
N73 36 506
N70 35 508
N71 35 508
N68 34 510
N69 34 510
N66 33 512
N67 33 512
N64 32 514
N65 32 514
N62 31 516
N63 31 516
N60 30 518
N61 30 518
N58 29 520
N59 29 520
N56 28 522
N57 28 522
N54 27 524
N55 27 524
N52 26 526
N53 26 526
N50 25 528
N51 25 528
N48 24 530
N49 24 530
N46 23 532
N47 23 532
N44 22 534
N45 22 534
N42 21 536
N43 21 536
N40 20 538
N41 20 538
N38 19 540
N39 19 540
N36 18 542
N37 18 542
N34 17 544
N35 17 544
N32 16 546
N33 16 546
N30 15 548
N31 15 548
N28 14 550
N29 14 550
N26 13 552
N27 13 552
N24 12 554
N25 12 554
N22 11 556
N23 11 556
N20 10 558
N21 10 558
N18 9 560
N19 9 560
N16 8 562
N17 8 562
N14 7 564
N15 7 564
N12 6 566
N13 6 566
N10 5 568
N11 5 568
N8 4 570
N9 4 570
N6 3 572
N7 3 572
N4 2 574
N5 2 574
N2 1 576
N3 1 576
N1 0 578578 rows selected. ELAPSED: 0 00:00:07.76 CPU: 0:00:07.38 BUFIO: 585 DIRIO: 1 FAULTS: 26
SQL>
刚才的测试结果是在公司里做的，表有记录578条，运行的结果相信你也看到了，耗时7.76秒，而且得到了全部结果。
现在在家里的PC上作一下测试，结果是5.56秒。我机器的配置：AMD DURON850, 394MB RAM, ORACLE 8.16。不明白你的测试结果为什么会有这么大的差异。当然这绝不是一种好办法，因为查找的纪录太多。我试了一下，当纪录数达到两千的时候，基本上就无法忍受了。所以我建立了一个FUNCTION，用来返回当前的RANK。CREATE OR REPLACE
Function Get_RANK(p_score number)
RETURN number IS
w_rank number(6);
BEGIN
select count(*) into w_rank
from score
where score > p_score;
return w_rank+1;
EXCEPTION
WHEN no_data_found THEN
return 1 ;
END Get_RANK;
/然后用
SELECT x.NAME, x.SCORE, get_rank(x.score) rank
FROM SCORE x
ORDER BY SCORE DESC
来查询，2000千条纪录耗时4.53秒。如果按你说的，1条第1名，2条第2名，后面还是第3名的话，要改一下FUNCTION
CREATE OR REPLACE
Function Get_RANK(p_score number)
RETURN number IS
w_rank number(6);
BEGIN
select count(distinct score) into w_rank --用distinct过滤掉所有重复值
from score
where score > p_score;
return w_rank+1;
EXCEPTION
WHEN no_data_found THEN
return 1 ;
END Get_RANK;
/查询结果耗时7.1秒
我用你说的:SELECT NAME, SCORE, (SELECT COUNT(*)+1 FROM SCORE Y WHERE Y.SCORE>X.SCORE)
FROM SCORE X
ORDER BY SCOR
方法在SQL-SERVER里试,不到3秒钟就出来了,可是在ORACL里却出错.
如果不愿意升级版本的话，还是用函数吧。
from table a,
(select 成绩, min(名次) 名次 from (select rownum 名次, 学号, 成绩 from table order by 成绩 desc)) b
where a.成绩=b.成绩
你的想法比我的好，只是在子查询里用ORDER BY也要ORACLE 8.16的版本才支持，低的版本也不能生成带ORDER BY的VIEW。
Oracle, 我的不懂. 呵呵
from table a,
(select 成绩, min(名次) 名次 from (select IDENTITY(int, 1,1) AS 名次, 学号, 成绩 from table group by 成绩 order by 成绩 desc)) b
where a.成绩=b.成绩
http://www.csdn.net/expert/topic/316/316810.shtm