MySql语句

Question

我想查询一下排名，优先成绩，然后时间，最后返回一个当前排名。

select count(ID) as rank from 表名 
where type=1 
and ID in (select ID from 表名 where type=1 and mark > 70) 
or (mark = 70 and times <=  '1.000')

我当时的想法是先找到成绩比我大的，然后再找成绩和我一样，时间比我短的，统计出人数，就知道自己在第几名了。

Answer 1

写了个DEMO,没用你这种方法
sql下载地址:http://pan.baidu.com/s/1skRp1et

查询指定学号排名:

SELECT *
FROM
  ( SELECT id, stu_no, `name`, mark, @curRank := @curRank + 1 AS rank
   FROM grade p,

     (SELECT @curRank := 0) r
   ORDER BY mark DESC ) a
WHERE a.stu_no = 201604009

查询全部排名

SELECT id, stu_no, `name`, mark, @curRank := @curRank + 1 AS rank
FROM grade p,

  (SELECT @curRank := 0) r
ORDER BY mark DESC

Answer 2

思路是对的,不过不需要子查询

select count(ID) + 1 as rank 
from 表名 
where type = 1 and (mark > 70 or (mark = 70 and times < '1.000'))