关于MYSQL索引排列的问题

Question

各位大大,小弟有个问题不解,MYSQL中,
比如说有以下表结构和内容id(自动增加) 姓名 语文成绩 数学成绩 物理成绩
1           AA  50      60      70
2           BB  55      65      75
3           CC  40      46      80
4           DD  34      70      50
.......如果我想按语文成绩或者数学或者物理成绩排列,如何得到查询某人的语文成绩排名?? 比如说用户"CC"按物理成绩排名第一,或者"DD"按数学成绩排列第一,我如何输出这个第一??假设数据库中数据庞大,不是小数据!
谢谢帮忙,结帖人人有分!

Answer 1

select *,
    (select count(*) from 表名 where 语文成绩>=t.语文成绩) as 语文排名,
    (select count(*) from 表名 where 数学成绩>=t.数学成绩) as 数学排名,
    (select count(*) from 表名 where 物理成绩>=t.物理成绩) as 物理排名
from 表名 as t

Answer 2

declare @ta table(id int identity(1,1),姓名 varchar(5), 语文成绩 int, 数学成绩 int,物理成绩 int)
insert @ta select 'AA',  50,      60,      70
insert @ta select 'BB',  55,      65,      75
insert @ta select 'CC',  40,      46,      80
insert @ta select 'DD',  34,      70,      50
select * ,
 [语文成绩排名]=(select count(1) from @ta where 语文成绩>a.语文成绩)+1,
 [数学成绩排名]=(select count(1) from @ta where 数学成绩>a.数学成绩)+1,
 [物理成绩排名]=(select count(1) from @ta where 物理成绩>a.物理成绩)+1
from @ta a(1 行受影响)(1 行受影响)(1 行受影响)(1 行受影响)
id          姓名    语文成绩        数学成绩        物理成绩        语文成绩排名      数学成绩排名      物理成绩排名
----------- ----- ----------- ----------- ----------- ----------- ----------- -----------
1           AA    50          60          70          2           3           3
2           BB    55          65          75          1           2           2
3           CC    40          46          80          3           4           1
4           DD    34          70          50          4           1           4(4 行受影响)

Answer 3

declare @ta table(id int identity(1,1),姓名 varchar(5), 语文成绩 int, 数学成绩 int,物理成绩 int)
insert @ta select 'AA',  50,      60,      70
insert @ta select 'BB',  55,      65,      75
insert @ta select 'CC',  40,      46,      80
insert @ta select 'DD',  34,      70,      50
select id,姓名,语文成绩,数学成绩,物理成绩,
[top 1]=( 
select top 1 a
from (
select 语文排名 as a union all
select 数学排名 union all
select 物理排名)ta order by right(a,2) asc)
from
(select * ,
 [语文排名]='语文排名'+rtrim((select count(1) from @ta where 语文成绩>a.语文成绩)+1)+'名',
 [数学排名]='数学排名'+rtrim((select count(1) from @ta where 数学成绩>a.数学成绩)+1)+'名',
 [物理排名]='物理排名'+rtrim((select count(1) from @ta where 物理成绩>a.物理成绩)+1)+'名'
from @ta a)tmpid          姓名    语文成绩        数学成绩        物理成绩        top 1
----------- ----- ----------- ----------- ----------- ----------------------
1           AA    50          60          70          语文排名2名
2           BB    55          65          75          语文排名1名
3           CC    40          46          80          物理排名1名
4           DD    34          70          50          数学排名1名