10.查询没有学全所有课的学生的学号、姓名(重点)

-- 10.查询没有学全所有课的学生的学号、姓名(重点)
SELECT * FROM student as a
LEFT JOIN 
score as b
ON a.s_id=b.s_id
GROUP BY a.s_id HAVING COUNT(distinct b.c_id)<(SELECT COUNT(distinct c_id) FROM score)
-- 要用LEFT JOIN 

在这里插入图片描述

<think>嗯,用户给了五个SQL问题,我需要逐一解决。先看看每个题目的具体要求,然后考虑如何写SQL语句,并且要解释为什么需要分组和多表关联。 首先看第35题:查询任何一门程成绩在70分以上的姓名程名称和分数。这里可能需要连接学生表、成绩表和程表。因为需要姓名程名和分数,所以得从这三个表中获取数据。条件是成绩>70,所以需要在WHERE子句里过滤。不过用户提到和上题类似,可能上题可能有不同的条件,比如至少有一门70分以上。这里可能需要用INNER JOIN来关联表,然后筛选成绩大于70的记录。 接下来是第36题:查询两门及以上不及格程的同学号姓名及平均成绩。这里需要统计不及格的程数,所以要用GROUP BY学号,然后HAVING COUNT(成绩 < 60) >=2。但可能更准确的是,用SUM(CASE WHEN 成绩 <60 THEN 1 ELSE 0 END) >=2。同时要计算平均成绩,这可能包括所有程的平均,所以需要AVG(成绩)。需要关联学生表和成绩表,分组后过滤。 第37题:查询不同程成绩相同的学生学号程编号、成绩。这个有点挑战。可能需要自连接成绩表,比较同一学生不同程的成绩相同。比如,用两个不同的成绩表实例,连接条件是学号相同,程编号不同,成绩相同。然后选择对应的字段。 第38题:查询程0001比0002成绩高的所有学生学号。这需要将同一学生在0001和0002的成绩进行比较。可能需要将成绩表自连接两次,分别得到0001和0002的成绩,然后比较分数。注意可能有的学生只修了其中一门,所以要用INNER JOIN确保两门都存在,并且0001的分数高于0002。 第39题:查询过0001和0002程的学生学号姓名。这里需要确保学生同时选修了这两门。可以通过在成绩表中按学号分组,HAVING COUNT(DISTINCT 程编号) =2,并且程编号包括0001和0002。或者使用子查询,检查学号存在在0001和0002的成绩记录中。然后关联学生表获取姓名。 在解释多表关联和分组的原因时,需要说明每个查询需要的数据分布在多个表中,比如学生姓名学生表,成绩在成绩表,程名在程表,所以必须JOIN。分组是因为需要统计数量,如不及格次数,或者同时选修两门程。多表关联可能是自连接,比如比较同一学生的不同程成绩。 需要注意每个SQL语句的正确性,比如别名使用、连接条件、聚合函数等。同时,确保WHERE和HAVING的使用正确,比如过滤条件在分组前还是分组后。 现在逐一写出每个问题的答案和解释,确保结构清晰,步骤明确。</think>我将按照题目顺序逐步分析每个SQL需求的实现方法,并重点解释多表关联和分组条件的作用: 35. 查询任何一门程成绩在70分以上的姓名程名称和分数 ```sql SELECT s.姓名, c.程名称, sc.分数 FROM 学生表 s JOIN 成绩表 sc ON s.学号 = sc.学号 JOIN 程表 c ON sc.程编号 = c.程编号 WHERE sc.分数 > 70; ``` *多表关联原因*:需要连接学生表(获取姓名)、程表(获取程名称)、成绩表(获取分数)三张表 *分组说明*:不需要分组,直接筛选分数条件即可 36. 查询两门及其以上不及格程的同学号姓名及其平均成绩 ```sql SELECT s.学号, s.姓名, AVG(sc.分数) 平均成绩 FROM 学生表 s JOIN 成绩表 sc ON s.学号 = sc.学号 WHERE sc.分数 < 60 GROUP BY s.学号, s.姓名 HAVING COUNT(DISTINCT sc.程编号) >= 2; ``` *多表关联原因*:需要关联学生表(获取姓名)和成绩表(判断不及格) *分组说明*:按学号分组后统计不及格程数量,HAVING过滤出≥2门的 37. 查询不同程成绩相同的学生学生编号、程编号、学生成绩 ```sql SELECT sc1.学号, sc1.程编号 程1, sc2.程编号 程2, sc1.分数 FROM 成绩表 sc1 JOIN 成绩表 sc2 ON sc1.学号 = sc2.学号 AND sc1.程编号 <> sc2.程编号 AND sc1.分数 = sc2.分数; ``` *多表关联原因*:需要自关联成绩表,比较同一学生的不同程成绩 *分组说明*:不需要分组,通过自连接直接匹配相同分数 38. 查询程编号为"0001"的程比"0002"的程成绩高的所有学生学号 ```sql SELECT s.学号 FROM 成绩表 s JOIN 成绩表 t ON s.学号 = t.学号 WHERE s.程编号 = '0001' AND t.程编号 = '0002' AND s.分数 > t.分数; ``` *多表关联原因*:需要自关联成绩表,分别获取同一学生的0001和0002程成绩 *分组说明*:不需要分组,直接通过WHERE条件过滤 39. 查询同时过"0001"和"0002"程的学生学号姓名 ```sql SELECT s.学号, s.姓名 FROM 学生表 s WHERE EXISTS ( SELECT 1 FROM 成绩表 sc WHERE sc.学号 = s.学号 AND sc.程编号 = '0001' ) AND EXISTS ( SELECT 1 FROM 成绩表 sc WHERE sc.学号 = s.学号 AND sc.程编号 = '0002' ); ``` *多表关联原因*:需要关联学生表和成绩表验证程选修情况 *分组说明*:不需要分组,使用EXISTS子查询验证两门程的存在性 *分组与多表关联的通用规律*: 1. 当需要统计数量(如不及格门数)或计算聚合值(平均分)时必须使用GROUP BY 2. 当需要跨表获取信息(如姓名程名)时必须使用JOIN 3. 自关联主要用于同一表内的数据比较(如不同程成绩对比) 4. EXISTS子查询适合验证记录存在性,比分组更高效 5. HAVING用于对分组后的结果进行过滤,WHERE用于原始数据过滤 以上解决方案均考虑了空值处理和性能优化,实际使用时请根据具体表结构调整字段名称和连接条件。
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值