sql分段统计

㈠ 图解SQL面试题：经典50题

已知有四张表，分别为学生表（student）、成绩表（score）、课程表（course）和教师表（teacher）。根据这些信息，需要编写SQL语句来解决以下问题。

首先，创建数据库和表。在客户端navicat中，创建学生表（student），设置“学号”为主键约束，并定义其他列的数据类型和约束。接着，创建成绩表（score），设置“课程号”和“学号”为主键约束，定义“成绩”列的数据类型。创建课程表（course），设置“课程号”为主键约束。创建教师表（teacher），设置“教师号”为主键约束。

然后，向表中添加数据。使用SQL语句和navicat操作向学生表（student）、成绩表（score）、课程表（course）和教师表（teacher）中添加数据。

接下来，进行50道面试题的解答。分类包括简单查询、汇总分析、复杂查询和多表查询。

简单查询包括：查询姓“猴”的学生名单，查询姓“孟”老师的个数。

汇总分析问题包括：查询课程编号为“0002”的总成绩，查询选了课程的学生人数，查询各科成绩最高和最低的分，查询每门课程被选修的学生数，查询男生、女生人数，查询平均成绩大于60分学生的学号和平均成绩，查询至少选修两门课程的学生学号，查询同名同姓学生名单并统计同名人数，查询不及格的课程并按课程号从大到小排列，查询每门课程的平均成绩，结果按平均成绩升序排序，平均成绩相同时，按课程号降序排列，检索课程编号为“0004”且分数小于60的学生学号，结果按分数降序排列，统计每门课程的学生选修人数（超过2人的课程才统计），要求输出课程号和选修人数，查询两门以上不及格课程的同学的学号及其平均成绩，查询学生的总成绩并进行排名，查询平均成绩大于60分的学生的学号和平均成绩。

复杂查询包括：查询所有课程成绩小于60分学生的学号、姓名，查询没有学全所有课的学生的学号、姓名，查询出只选修了两门课程的全部学生的学号和姓名，查询1990年出生的学生名单，查询各科成绩前两名的记录。

多表查询问题包括：查询所有学生的学号、姓名、选课数、总成绩，查询平均成绩大于85的所有学生的学号、姓名和平均成绩，查询学生的选课情况：学号，姓名，课程号，课程名称，查询出每门课程的及格人数和不及格人数，使用分段[100-85],[85-70],[70-60],[<60]来统计各科成绩，分别统计：各分数段人数，课程号和课程名称，查询课程编号为0003且课程成绩在80分以上的学生的学号和姓名。

高级功能：窗口函数，包括查询学生平均成绩及其名次，按各科成绩进行排序并显示排名，查询每门功成绩最好的前两名学生姓名，查询所有课程的成绩第2名到第3名的学生信息及该课程成绩，查询各科成绩前三名的记录。

解答这50道面试题需要理解SQL的基础语法，熟悉表之间的关联关系，并掌握查询语句的编写技巧。通过实际操作和理解题目的逻辑，可以有效地提升SQL编程能力。

㈡ SQL语句的多表查询

在处理SQL多表查询时，我们需要关注几个关键点：合并两个表、内联结、左联结、右联结、查询业务逻辑、平均成绩筛选、选课情况统计、成绩段统计、分组查询和特定数据的筛选。

开始练习合并两个表时，使用 UNION ALL 语句可以将两个表的数据合并，并保持重复项。这种操作对数据整合特别有用。

内联结涉及到在 from 子句中同时使用两张表，并通过别名（如 student as a）来区分它们。在列名中，我们使用 "a.列名" 来引用特定表的数据。使用 INNER JOIN 来筛选同时存在于两张表中的数据，通过 ON 子句定义关联条件（例如，通过学号）。内联结就像是建立了一座桥梁，连接了两个表。

左联结（LEFT JOIN）允许我们保留表A中的所有记录，即使在表B中没有匹配的记录。我们通过添加 WHERE 子句来过滤结果，确保只包括表A中的数据。这里的 is null 判断用于找出表B中学号为空值的记录。

右联结（RIGHT JOIN）则反之，保留表B中的所有记录，即使在表A中没有匹配项。同样，WHERE 子句可以用于过滤结果，确保只包括表B中的数据。

为了获取所有学生的学号、姓名、选课数和总成绩，我们需要翻译业务需求为SQL语句，使用 SELECT 子句列出所需字段，使用 JOIN 连接表，并可能使用聚合函数如 COUNT 或 SUM 来计算选课数和总成绩。

查询平均成绩大于85的所有学生信息，可以使用 SELECT 和 WHERE 子句来筛选满足条件的记录，并使用 AVG 函数计算平均成绩。

查询学生的选课情况，包括学号、姓名、课程号和课程名，使用 SELECT 子句列出相关字段，并使用 JOIN 连接表。

统计每门课程的及格人数和不及格人数，使用 CASE 表达式来判断成绩是否及格，并在 SELECT 子句中使用 COUNT 函数来计数。确保 CASE 表达式遵循正确的语法结构，包括 else 和 end。

使用分段统计各科成绩的人数，可以使用 CASE 表达式对成绩进行分类，并使用 GROUP BY 子句来对结果进行分组。注意，即使在 GROUP BY 子句中使用了特定字段（如课程名称），分组结果仍然基于成绩段。

最终，通过掌握这些基本技巧和理解 SQL 的联结操作，可以有效地处理多表查询任务，并解决各种业务逻辑问题。

㈢ SQL：对score表进行分段统计

select 课程编号,
sum(case when 成绩=N'优' then 1 else 0 end) 优,
sum(case when 成绩=N'良' then 1 else 0 end) 良,
sum(case when 成绩=N'中' then 1 else 0 end) 中,
sum(case when 成绩=N'差' then 1 else 0 end) 差,
...........

count(*) as 人数合计
from score
group by 课程编号