作为一个例子,表:[code] create table student (id int not null auto_increment, point int not null default 0, fullname varchar(16) not null, primary key(id)); [/code]数据:[code] mysql> desc student; +----------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------+-------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | point | int(11) | NO | | 0 | | | fullname | varchar(16) | NO | | | | +----------+-------------+------+-----+---------+----------------+ 3 rows in set (0.00 sec)mysql> select * from student order by point desc; +----+-------+----------+ | id | point | fullname | +----+-------+----------+ | 2 | 92 | user3 | | 3 | 92 | user2 | | 1 | 90 | user1 | | 4 | 85 | user4 | +----+-------+----------+ 4 rows in set (0.00 sec)mysql> [/code]在这里面,user3, user2 排名第一,user1 排名第三,现在我要查第二名是谁,一查,没有第2名的。那就把第一名返回,就是这里的 user1和user2了。
没贴上表:create table student (id int not null auto_increment, point int not null default 0, fullname varchar(16) not null, primary key(id)); 数据;mysql> select * from student order by point desc; +----+-------+----------+ | id | point | fullname | +----+-------+----------+ | 2 | 92 | user3 | | 3 | 92 | user2 | | 1 | 90 | user1 | | 4 | 85 | user4 | +----+-------+----------+ 4 rows in set (0.00 sec)mysql>
"就是这里的 user1和user2了。" => 就是这里的 user3和user2了。
select * from student where point = (select point from student order by point desc limit 2,1)
select * from student where point = (select point from student order by point desc limit 1,1)这个显示的是第2,但是没有第二,所以显示2个第一
select * from student group by 成绩 order by 成绩 limit 1, 10
不是,我是用来说明排名的计算方法的,表里没这个 rank 项。
用什么字段来排序?是否有主键?.
[align=center]==== 思想重于技巧 ====
[/align]
.
贴子分数<20:对自已的问题不予重视。
贴子大量未结:对别人的回答不予尊重。
.
作为一个例子,表:[code]
create table student
(id int not null auto_increment,
point int not null default 0,
fullname varchar(16) not null, primary key(id));
[/code]数据:[code]
mysql> desc student;
+----------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| point | int(11) | NO | | 0 | |
| fullname | varchar(16) | NO | | | |
+----------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)mysql> select * from student order by point desc;
+----+-------+----------+
| id | point | fullname |
+----+-------+----------+
| 2 | 92 | user3 |
| 3 | 92 | user2 |
| 1 | 90 | user1 |
| 4 | 85 | user4 |
+----+-------+----------+
4 rows in set (0.00 sec)mysql>
[/code]在这里面,user3, user2 排名第一,user1 排名第三,现在我要查第二名是谁,一查,没有第2名的。那就把第一名返回,就是这里的 user1和user2了。
(id int not null auto_increment,
point int not null default 0,
fullname varchar(16) not null, primary key(id));
数据;mysql> select * from student order by point desc;
+----+-------+----------+
| id | point | fullname |
+----+-------+----------+
| 2 | 92 | user3 |
| 3 | 92 | user2 |
| 1 | 90 | user1 |
| 4 | 85 | user4 |
+----+-------+----------+
4 rows in set (0.00 sec)mysql>
| id | point | fullname | s1 | s2 |
+----+-------+----------+-----+----+
| 2 | 92 | user3 | 1 | 2 |
| 3 | 92 | user2 | 1 | 2 |
| 1 | 90 | user1 | 3 | |
| 4 | 85 | user4 | 4 | |
+----+-------+----------+-----+----+ 这个方法以前的贴子中介绍了很多次了。一句SQL,然后你加个where s1=2 or s2=2即可.
[align=center]==== 思想重于技巧 ====
[/align]
.
贴子分数<20:对自已的问题不予重视。
贴子大量未结:对别人的回答不予尊重。
.