楼主也最好用你自己的数据做一下测试。2#的语句我用你提供的测试数据没有问题。mysql> select * from t_xxm712; +------+------+-------+ | id | name | grade | +------+------+-------+ | 1 | 2 | 80 | | 2 | 2 | 90 | | 3 | 3 | 80 | | 4 | 4 | 88 | +------+------+-------+ 4 rows in set (0.00 sec)mysql> select id,name,grade,(select count(*) from t_xxm712 where grade>a.grade)+1 as rank -> from t_xxm712 a -> order by grade desc; +------+------+-------+------+ | id | name | grade | rank | +------+------+-------+------+ | 2 | 2 | 90 | 1 | | 4 | 4 | 88 | 2 | | 1 | 2 | 80 | 3 | | 3 | 3 | 80 | 3 | +------+------+-------+------+ 4 rows in set (0.00 sec)mysql>
Oracle select t1.id, t1.name, t1.grade, t2.rank from tb1 t1 left join ( select rownum as rank, grade from (select distinct grade from tb1 order by grade desc)) t2 on t1.grade=t2.grade order by t1.id
-----My SQL方法(变量法) --当然:在你每次在执行这个查询前,先把递增变量变为0) set @rownum=0;select t1.id, t1.name, t1.grade, t2.rank from tb1 t1 right join (select distinct @rownum:=@rownum+1 as rank, grade from tb1 order by grade desc) t2 on t1.grade=t2.grade order by t1.id;--My SQL目前还没有好的类似于Oracle的row_number函数
--1、2楼已经是正解? 正解个屁,你多插入一行( 5 5 88 )试试! --按成绩排名 id name grade 1 2 80 2 2 90 3 3 80 4 4 88 5 5 88--是不是结果应该是: id name grade rank 1 2 80 3 2 2 90 1 3 3 80 3 4 4 88 2 5 5 88 2--可是你去运行1、2楼的SQL语句看看,看看是不是上面这个结果! select id,name,grade,(select count(*) from yourTable where grade>a.grade)+1 as rank from yourTable order by grade desc;--------My SQL方法一:(Distinct子查询法)-------------------- --优点:不用去定义变量,省事 --缺点:运行效率不如方法二 select id, name, grade, (SELECT count(distinct grade) from tb1 where grade>t1.grade)+1 as rank from tb1 t1 order by grade desc;--------My SQL方法二:(变量递增法)-------------------------- --优点:速度应该比方法一快 --缺点:在每次运行此查询之前,要定义并初始化为0的变量,用以排名,比较麻烦! set @rownum=0;select t1.id, t1.name, t1.grade, t2.rank from tb1 t1 right join (select distinct @rownum:=@rownum+1 as rank, grade from tb1 order by grade desc) t2 on t1.grade=t2.grade order by t1.id;--------Oracle方法一:(rownum右联接法)---------------------- select t1.id, t1.name, t1.grade, t2.rank from tb1 t1 left join ( select rownum as rank, grade from (select distinct grade from tb1 order by grade desc)) t2 on t1.grade=t2.grade order by t1.id
from yourTable
order by grade
from yourTable
order by grade desc
不对啊 还少了个函数,我就是不知道那函数叫什么 ,不知道您看没看到 80 分的都是第三名 你想的SQL不符合
+------+------+-------+
| id | name | grade |
+------+------+-------+
| 1 | 2 | 80 |
| 2 | 2 | 90 |
| 3 | 3 | 80 |
| 4 | 4 | 88 |
+------+------+-------+
4 rows in set (0.00 sec)mysql> select id,name,grade,(select count(*) from t_xxm712 where grade>a.grade)+1 as rank
-> from t_xxm712 a
-> order by grade desc;
+------+------+-------+------+
| id | name | grade | rank |
+------+------+-------+------+
| 2 | 2 | 90 | 1 |
| 4 | 4 | 88 | 2 |
| 1 | 2 | 80 | 3 |
| 3 | 3 | 80 | 3 |
+------+------+-------+------+
4 rows in set (0.00 sec)mysql>
select t1.id, t1.name, t1.grade, t2.rank
from tb1 t1 left join ( select rownum as rank, grade from (select distinct grade from tb1 order by grade desc)) t2
on t1.grade=t2.grade
order by t1.id
--当然:在你每次在执行这个查询前,先把递增变量变为0)
set @rownum=0;select t1.id, t1.name, t1.grade, t2.rank
from tb1 t1 right join (select distinct @rownum:=@rownum+1 as rank, grade from tb1 order by grade desc) t2
on t1.grade=t2.grade
order by t1.id;--My SQL目前还没有好的类似于Oracle的row_number函数
???凑巧 我看到很多人这样写sql语句了 用>做比较
-- 一句话:实践是检验真理的唯一标准!
--按成绩排名
id name grade
1 2 80
2 2 90
3 3 80
4 4 88
5 5 88--是不是结果应该是:
id name grade rank
1 2 80 3
2 2 90 1
3 3 80 3
4 4 88 2
5 5 88 2--可是你去运行1、2楼的SQL语句看看,看看是不是上面这个结果!
select id,name,grade,(select count(*) from yourTable where grade>a.grade)+1 as rank
from yourTable
order by grade desc;--------My SQL方法一:(Distinct子查询法)--------------------
--优点:不用去定义变量,省事
--缺点:运行效率不如方法二
select id, name, grade, (SELECT count(distinct grade) from tb1 where grade>t1.grade)+1 as rank
from tb1 t1
order by grade desc;--------My SQL方法二:(变量递增法)--------------------------
--优点:速度应该比方法一快
--缺点:在每次运行此查询之前,要定义并初始化为0的变量,用以排名,比较麻烦!
set @rownum=0;select t1.id, t1.name, t1.grade, t2.rank
from tb1 t1 right join (select distinct @rownum:=@rownum+1 as rank, grade from tb1 order by grade desc) t2
on t1.grade=t2.grade
order by t1.id;--------Oracle方法一:(rownum右联接法)----------------------
select t1.id, t1.name, t1.grade, t2.rank
from tb1 t1 left join ( select rownum as rank, grade from (select distinct grade from tb1 order by grade desc)) t2
on t1.grade=t2.grade
order by t1.id