sql查询3

1查询出只选修了一门课程的全部学生的学号和姓名

最初想法是select count(*)然后再去比较count(*),要是必就不会了

select s_id ,sname from student,sc where sc.s_id=student.id
group by sc.s_id,sname having count(*)=1;

2查询同名同性学生名单,并统计同名人数 

开始是让sc的2个表来比较同名的,然后发现错了多比了几遍,然后又想把id也相同。但是错的更离谱

select sname count(sname) from student group by id having count(sname)>1

3查询每门课程的平均成绩,结果按平均成绩升序排列,平均成绩相同时,按课程号降序排列 

Select C#,Avg(score) from SC group by C# order by Avg(score),C# DESC ; 

4查询平均成绩大于85的所有学生的学号、姓名和平均成绩

本意是用

 

select s_id from sc group by s_id having avg(score)>85;

 

有的s_id,然后再

select sname ,s_id avg(score) from sc,student where sc.s_id=student.id and s_id in 
(select avg(score),s_id from sc group by s_id having avg(score)>65) group by student.id;

但是sc.s_id=student.id and s_id ,结果

select sname,sc.s_id,avg(score) from sc,student where sc.s_id=student.id
group by s_id,sname having avge(score) >85;

sql查询3,古老的榕树,5-wow.com

郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。