56,679
社区成员
发帖
与我相关
我的任务
分享
+----+-------+----------+-----+----+
| id | point | fullname | s1 | s2 |
+----+-------+----------+-----+----+
| 2 | 92 | user3 | 1 | 2 |
| 3 | 92 | user2 | 1 | 2 |
| 1 | 90 | user1 | 3 | |
| 4 | 85 | user4 | 4 | |
+----+-------+----------+-----+----+
select * from student group by 成绩 order by 成绩 limit 1, 10
create table student
(id int not null auto_increment,
point int not null default 0,
fullname varchar(16) not null,
primary key(id));
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>
select * from student where point = (select point from student order by point desc limit 1,1)
select * from student where point = (select point from student order by point desc limit 2,1)