Question

数据库SQL的查询操作，查询结果增加2列用户自定义的逻辑

一个数据表有3个字段S(id,SName,Score)
id Sname score
1 小王 90
2 小2 70
3 小3 70
4 小4 40
5 小5 30

经过查询显示出
sname score mark rank
小王 90 pass 1
小2 70 pass 2
小3 70 pass 2
小4 40 fail 3
小5 30 fail 4
如何实现这个功能？
补充一下 是MS数据库

Answer 1

什么数据库？

ORACLE /DB2
SELECT Sname, score,
CASE WHEN score > 60 THEN 'pass' ELSE 'fail' END AS mark,
DENSE_RANK() OVER(ORDER BY score DESC) AS rank
FROM S

其他的可以用这个
SELECT Sname, score,
CASE WHEN score > 60 THEN 'pass' ELSE 'fail' END AS mark,
(SELECT count(DISTINCT score)+1 FROM S WHERE score > t.score) AS rank
FROM S t

MS 2005以上可以尝试上面那个，如果不行，就用下面这个通用的肯定可以

Answer 2

select sname,score, (case when score>=60 then 'pass' else 'fail' end) as mark,
(case when score>=90 then 1
when score>=70 then 2
when case score>=40 then 3
when score>=30 then 4 end) as rank
from tab