力扣---高频SQL题---12---1280. 学生们参加各科测试的次数

最新推荐文章于 2024-07-19 23:59:56 发布

@白圭

最新推荐文章于 2024-07-19 23:59:56 发布

阅读量216

点赞数 1

文章标签： leetcode sql 算法

本文链接：https://blog.csdn.net/taiyuezyh/article/details/136262562

版权

本文介绍了如何通过MySQL查询来统计学生参加各科目的考试情况，使用了右连接和左连接的方法，并展示了自连接在没有额外条件时的应用。最终目的是确定每个学生各科目是否已考试。

摘要由CSDN通过智能技术生成

一、题目

在这里插入图片描述

二、代码

# Write your MySQL query statement below   

#初步成形   有部分学生有科目没考考

-- select Students.student_id , Students.student_name ,Examinations.subject_name , count(Examinations.subject_name) as subject_name from Students 
-- right join Examinations 
-- on Examinations.student_id  = Students.student_id
-- group by Students.student_id,Examinations.subject_name
-- order by Students.student_id, Examinations.subject_name asc 


-- select Examinations.subject_name , Examinations.student_id  from Subjects 
-- left join  Examinations 
-- on Subjects.subject_name  = Examinations.subject_name

#自连接 不用条件可以直接连接对吗

select table1.student_id, table1.student_name ,table1.subject_name, coalesce(attended_exams, 0) as attended_exams from
(
    select * from Students 
    join Subjects 
    order by Students.student_id,Subjects.subject_name asc
)  as table1
left join
(
    select student_id , subject_name ,count(subject_name ) as attended_exams from Examinations
    group by student_id, subject_name  
) as table2
on table1.student_id  = table2.student_id
and table1.subject_name   = table2.subject_name 
order by table1.student_id, table1.subject_name  asc