原题:
有一张成绩表(course),只有两个字段,姓名(name)和成绩(score)。怎样用
一个SQL语句查询出某个学生的姓名,成绩以及在成绩表中的排名?
表结构如下:
表名:Course
姓名字段:name
成绩字段:score我写的SQL语句:
SELECT name, score,
(SELECT COUNT(name)
FROM Course
WHERE score >=
(SELECT score
FROM Course
WHERE name =@name)) AS mycount
FROM course
WHERE (name = @name)
各位 这样子可以么
有一张成绩表(course),只有两个字段,姓名(name)和成绩(score)。怎样用
一个SQL语句查询出某个学生的姓名,成绩以及在成绩表中的排名?
表结构如下:
表名:Course
姓名字段:name
成绩字段:score我写的SQL语句:
SELECT name, score,
(SELECT COUNT(name)
FROM Course
WHERE score >=
(SELECT score
FROM Course
WHERE name =@name)) AS mycount
FROM course
WHERE (name = @name)
各位 这样子可以么
SELECT name, score,
(SELECT COUNT(name)
FROM Course
WHERE score >=
(SELECT score
FROM Course
WHERE name =a.name)) AS mycount
FROM course a
WHERE (name = a.name)
(SELECT COUNT(name)
FROM Course
WHERE score >= a.score) AS mycount
FROM course a
--WHERE (name = @name) order by name
就要注意啦如果分数相等但名次一样且不保锱名次空缺的话就有问题
eg: N001 90 名次为20
N002 90 名次也来20
N003 89 名次不留空缺为21如果为以上这种方式来统计名次的话,你的语句就有问题
(SELECT COUNT(*) + 1
FROM course a
WHERE a.score> b.score) AS pm
FROM course b
ORDER BY pm
(
name varchar(10),
score int
)insert A select 'Sc',98
insert A select 'PPc',80
insert A select 'ERE',98select T.*,(select count(distinct score) from A where score>=T.score)
from A T
create table T_STU
(
name varchar(6),
score float
)
insert into t_stu
select '1',100 union all
select '2',80 union all
select '3',90 union all
select '4',80 union all
select '5',95 union all
select '6',60 select * from t_stu where 1 = 1Select name , score , (select count(*) from t_stu where a.score < score ) + 1 as 名次
from t_stu a
order by score desc,
name asc