mysql 查询每门成绩的前十名

mysql 查询每门成绩的前十名

mysql 查询每门成绩的前十名
create table sc
(sno int,
cno int,
score);

insert into sc values (1,1,100);
insert into sc values (2,1,80);
insert into sc values (3,1,25);
insert into sc values (4,1,45);
insert into sc values (5,1,67);
insert into sc values (1,2,25);
insert into sc values (2,2,77);
insert into sc values (3,2,78);
insert into sc values (4,2,69);
insert into sc values (5,2,24);2017-08-22

速度快是不可能了，我就随便按照你说的列点数据，只取前两名，你领会一下吧

建表及数据
create table sc(sno int,cno int,score);insert into sc values (1,1,100);insert into sc values (2,1,80);insert into sc values (3,1,25);insert into sc values (4,1,45);insert into sc values (5,1,67);insert into sc values (1,2,25);insert into sc values (2,2,77);insert into sc values (3,2,78);insert into sc values (4,2,69);insert into sc values (5,2,24);
执行
select sno,cno,scorefrom sc r1 where (select count(1) from sc r2 where r2.cno=r1.cno and r2.score >= r1.score) <=2;
结果截图

2013-09-15

CREATE PROCEDURE GetScRank()BEGIN DECLARE SigDone INT DEFAULT 0; DECLARE Cno_ BIGINT; DECLARE Cur1 CURSOR FOR SELECT DISTINCT Cno FROM Score; DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET SigDone = 1; OPEN Cur1; TRUNCATE scResult; FETCH Cur1 INTO Cno_; WHILE NOT SigDone DO INSERT INTO scResult (Sno,Cno,Sc) (SELECT DISTINCT Sno,Cno,Sc FROM Score WHERE Cno =Cno_ ORDER BY Sc DESC LIMIT 10); COMMIT; FETCH Cur1 INTO Cno_; END WHILE; CLOSE Cur1;END用游标效率应该还行吧.2013-09-16

order by + limit可以取到前10啊，你要有其他操作，查出的数据作为一个虚拟表再处理2017-08-16

limit加一层嵌套的话就可以用在子查询了，就是你子查询里面再来一层子查询，最里面那层我记得可以用2013-09-15