筛选、对比筛选、group_concat的练习

是 否 +2 论坛币

k人 参与回答

经管之家送您一份

应届毕业生专属福利!

求职就业群

赵安豆老师微信：zhaoandou666

经管之家联合CDA

送您一个全额奖学金名额~ !

立即领取

感谢您参与论坛问题回答

经管之家送您两个论坛币！

+2 论坛币

（1）查询"01"课程比"02"课程成绩高的学生信息及课程分数(选修的每一门课程的分数)

select stu.*,sc.c_id,sc.score

from stu

join (select * from sc where c_id="01") as a1 on stu.s_id=a1.s_id

join (select * from sc where c_id="02") as a2 on stu.s_id=a2.s_id

join sc on stu.s_id=sc.s_id

where a1.score>a2.score

;

（2）查询学过"张三"老师授课的同学的信息

select stu.*

from stu

join sc on stu.s_id=sc.s_id

join co on sc.c_id=co.c_id

join te on co.t_id=te.t_id

where te.t_name="张三"

;

（3）查询选修"张三"老师所授课程的学生中，成绩最高的学生信息及其成绩

select stu.*,score

from stu

join sc on stu.s_id=sc.s_id

join co on sc.c_id=co.c_id

join te on co.t_id=te.t_id

where te.t_name="张三" and score=(

select max(score)

from stu

join sc on stu.s_id=sc.s_id

join co on sc.c_id=co.c_id

join te on co.t_id=te.t_id

where te.t_name="张三");

（4）查询没学过"张三"老师授课的同学的信息

select *

from stu

where s_id not in (

select stu.s_id

from stu

left join sc on stu.s_id=sc.s_id

left join co on sc.c_id=co.c_id

left join te on co.t_id=te.t_id

where te.t_name="张三")

;

（5）查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息

方法1：

select stu.*

from stu

left join sc on stu.s_id=sc.s_id

where c_id in ("01","02")

group by stu.s_id

having count(c_id)=2

方法2：

select stu.*

from stu

left join sc on stu.s_id=sc.s_id

where c_id in ("01","02")

group by stu.s_id

having group_concat(c_id)="01,02";

（6）查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息

方法1：查询出学过编号为“01”、“02”的同学，然后用group_concat将课程形成字符连接，其中为“01”的即为题目所求

select *

from stu

join sc on stu.s_id=sc.s_id

where sc.c_id in ("01","02")

group by stu.s_id

having group_concat(c_id)="01";

方法2

select *

from sc

left join stu on stu.s_id=sc.s_id

where sc.c_id=01

and stu.s_id not in (

select sc.s_id from sc

left join stu on stu.s_id=sc.s_id

where sc.c_id=02)

;

（7）查询和"01"号的同学学习的课程完全相同的其他同学的信息

select stu.*

from sc

left join stu on sc.s_id=stu.s_id

group by sc.s_id

having group_concat(sc.c_id)=(select group_concat(c_id) from sc where s_id="01") and sc.s_id!="01"

;

（8）按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩

方法1

select

s_id,

sum(if(c_id="01",score,0)) as 课程01,

sum(if(c_id="02",score,0)) as 课程02,

sum(if(c_id="03",score,0)) as 课程03,

avg(score) as 平均成绩

from sc

group by s_id

order by 平均成绩 desc;

方法2

select

s_id,

sum((c_id="01")*score) as 课程01,

sum((c_id="02")*score) as 课程02,

sum((c_id="03")*score) as 课程03,

avg(score)

from sc

group by s_id

order by avg(score) desc;

（9）查询各科成绩最高分、最低分和平均分：以如下形式显示：课程ID，课程name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率

select sc.c_id,c_name,max(score) 最高分,min(score) 最低分,avg(score) 平均分,

avg(score>=60) 及格率,

avg(score>=70 and score<80) 中等率,

avg(score>=80 and score<90) 优良率,

avg(score>=90) 优秀率

from sc

left join co on sc.c_id=co.c_id

group by sc.c_id;

扫码加我 拉你入群

请注明：姓名-公司-职位

以便审核进群资格，未注明则拒绝

分享0 收藏0 回帖

关键词：Group CAT con