小玲-Eleen-CSDN博客

原创查询两门及其以上不及格课程的同学的学号平均成绩

select a.s_no,a.avg_scorefrom (select test.score.S_no,round(avg(test.score.S_score),2)avg_score from test.score group by test.score.S_no)awhere a.s_no in (select test.score.S_no from test.score where test.score.S_score<60 group by test.score.S_no hav.

2021-08-30 17:27:41 947

原创查询学过编号为“01“并且也学过编号为“02“的课程的同学的信息

select a.* from student a,score b,score c where a.s_id = b.s_id and a.s_id = c.s_id and b.c_id='01' and c.c_id='02'

2021-08-30 16:19:04 2090 2

原创查询“01“课程比“02“课程成绩高的学生的信息及课程分数

select test.c.S_no,test.student.s_name,test.c.c_no,test.c.S_scorefrom test.score c,test.studentwhere test.c.s_no=test.student.s_no and test.c.S_no in (select a.s_no from (select test.score.S_no,test.score.C_no,test.score.S_score from test.score where tes

2021-08-30 15:09:52 704

原创查询各学生的年龄-- 按照出生日期来算，当前月日＜出生年月的月日则，年龄减一

select s_birth,(DATE_FORMAT(NOW(),'%Y')-DATE_FORMAT(s_birth,'%Y') - (case when DATE_FORMAT(NOW(),'%m%d')>DATE_FORMAT(s_birth,'%m%d') then 0 else 1 end)) as age

2021-08-30 14:15:14 4948 1

原创查询每门功成绩最好的前两名点赞的写法

select a.s_id,a.c_id,a.s_score from score a where (select COUNT(1) from score b where b.c_id=a.c_id and b.s_score>=a.s_score)<=2 ORDER BY a.c_id

2021-08-30 13:44:29 396

原创查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩点赞的写法

select a.s_id,a.c_id,a.s_score from score a where (select COUNT(1) from score b where b.c_id=a.c_id and b.s_score>=a.s_score)<=2 ORDER BY a.c_id

2021-08-30 13:19:03 3738

原创统计各科成绩各分数段人数、课程编号、课程名称

select A.c_no, A.c_name,sum(case when A.s_score>=85 then 1 else 0 end) as '85-100',sum(case when A.s_score>=70 and A.s_score<85 then 1 else 0 end) as '70-85',sum(case when A.s_score>=60 and A.s_score<70 then 1 else 0 end) as '60-70',s...

2021-08-27 17:07:39 1927

原创查询学生男女人数

select test.student.S_sex,count(test.student.S_sex)'人数'from test.studentgroup by test.student.s_sex

2021-08-27 17:07:12 2847 1

原创按照各科成绩排序按照总成绩排序

按各科成绩进行排序，并显示排名set @rank=0;select test.score.*,test.student.S_name,(@rank:=@rank+1)排名from test.score,test.studentwhere test.score.s_no=test.student.s_noorder by test.score.s_score查询学生的总成绩并进行排名@rank := @rank + 1不能和group by 一起使用解决办法: 将分组计算的结果作为一

2021-08-25 14:18:19 1164

原创查询各科成绩最高分、最低分、平均分以如下形式显示：课程ID、课程NAME、最高分、最低分、及格率、中等率、优良率、优秀率、不及格率及格为＞=60 中等为70-80 优良为80-90 优秀为＞=90

select test.score.c_no,test.course.c_name,test.score.s_score,round(avg(test.score.s_score),2)avg_score,min(test.score.s_score)min_score,max(test.score.s_score)max_score,round(100*(sum(case when test.score.s_score>=60 and test.score.s_score<70 th

2021-08-25 13:44:12 3565

原创按照平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩

select test.score.* ,test.student.s_name,test.student.s_birth,test.student.s_sex, test.course.c_name,test.course.t_no,test.teacher.t_name,A.avg_scorefrom test.scoreleft join test.student on test.student.s_no=test.score.s_noleft join test.course on te..

2021-08-25 11:28:09 1505

weixin_60715943的博客