mysql查询每门功成绩最好的前两名

版权声明:本文为博主原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。
本文链接:https://blog.csdn.net/pkgray/article/details/86190707
DROP TABLE IF EXISTS `score`;

CREATE TABLE `score` (
  `s_id` varchar(20) NOT NULL DEFAULT '',
  `c_id` varchar(20) NOT NULL DEFAULT '',
  `s_score` int(3) DEFAULT NULL,
  PRIMARY KEY (`s_id`,`c_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/*Data for the table `score` */

insert  into `score`(`s_id`,`c_id`,`s_score`) values ('01','01',80),('01','02',90),('01','03',99),('02','01',70),('02','02',60),('02','03',80),('03','01',80),('03','02',80),('03','03',80),('04','01',50),('04','02',30),('04','03',20),('05','01',76),('05','02',87),('06','01',31),('06','03',34),('07','02',89),('07','03',98);
SELECT s1.* FROM score s1 WHERE
(
SELECT COUNT(1) FROM score s2 WHERE
s1.c_id=s2.c_id AND s2.s_score>=s1.s_score
)<=2
ORDER BY s1.c_id,s1.s_score DESC;

https://bbs.csdn.net/topics/392323240

展开阅读全文

SQL语句 获取到每门课程,成绩最高的 前两名 同学

10-12

[code=SQL]rnuse masterrngorncreate database testrngornuse testrngornif exists(select 1 from sys.objects where name='tCourse' and type='U')rndrop table tCourserngorncreate table tCourse--课程rn(rn id int identity primary key,rn department nvarchar(20),rn Course nvarchar(20)rn)rninsert into tCourse values('计算机系','数学')rninsert into tCourse values('计算机系','C语言')rninsert into tCourse values('计算机系','英语')rninsert into tCourse values('外语系','语文')rninsert into tCourse values('外语系','英语')rninsert into tCourse values('外语系','政治')rninsert into tCourse values('数学系','语文')rninsert into tCourse values('数学系','数学')rninsert into tCourse values('数学系','政治')rninsert into tCourse values('外语系','数学')rngornif exists(select 1 from sys.objects where name='student' and type='U')rndrop table studentrngorncreate table student--学生rn(rn id int identity primary key,rn name nvarchar(20),rn department nvarchar(20)rn)rninsert into student values('张三','计算机系')rninsert into student values('李四','外语系')rninsert into student values('王二','计算机系')rninsert into student values('赵五','数学系')rngornif exists(select 1 from sys.objects where name='tScore' and type='U')rndrop table tScorerngorncreate table tScore--成绩rn(rn id int identity,rn studentId int references student(id),rn courseId int references Tcourse(id),rn score intrn)rninsert into tScore values(1,1,90)rninsert into tScore values(1,2,70)rninsert into tScore values(1,3,60)rninsert into tScore values(2,4,78)rninsert into tScore values(2,5,81)rninsert into tScore values(2,6,49)rninsert into tScore values(2,10,89)rninsert into tScore values(4,8,100)rninsert into tScore values(4,9,54)rninsert into tScore values(3,1,39)rngornselect * from studentrnselect * from tCoursernselect * from tScorern[/code]rn求sql:rn获取到每门课程,成绩最高的[b]前两名[/b]同学rn显示为:rnname Course Scorern赵五 数学 100rn张三 数学 90rn李四 语文 100rn张三 语文 95rn...... 论坛

没有更多推荐了,返回首页