pythondata 解法, 执行用时: 376 ms, 内存消耗: 60.2 MB, 提交时间: 2023-08-09 15:37:07

import pandas as pd

def students_and_examinations(students: pd.DataFrame, subjects: pd.DataFrame, examinations: pd.DataFrame) -> pd.DataFrame:
    # 按 id 和科目分组，并计算考试次数。
    grouped = examinations.groupby(['student_id', 'subject_name']).size().reset_index(name='attended_exams')

    # 获取 (id, subject) 的所有组合
    all_id_subjects = pd.merge(students, subjects, how='cross')

    # 左连接以保留所有组合。
    id_subjects_count = pd.merge(all_id_subjects, grouped, on=['student_id', 'subject_name'], how='left')
    
    # 数据清理
    id_subjects_count['attended_exams'] = id_subjects_count['attended_exams'].fillna(0).astype(int)
    
    # 根据'student_id'，'Subject_name'以升序对 DataFrame 进行排序。
    id_subjects_count.sort_values(['student_id', 'subject_name'], inplace=True)

    return id_subjects_count[['student_id', 'student_name', 'subject_name', 'attended_exams']]

pythondata 解法, 执行用时: 460 ms, 内存消耗: 61.1 MB, 提交时间: 2023-08-09 15:35:15

import pandas as pd

def students_and_examinations(students: pd.DataFrame, subjects: pd.DataFrame, examinations: pd.DataFrame) -> pd.DataFrame:
    # 创建所有可能的学生和科目组合
    student_subject_combinations = pd.merge(students, subjects, how='cross')
    
    # 为每个考试出席添加一个值为1的列
    examinations['attended_exams'] = 1
    
    # 将组合与考试表合并以获取出席考试次数
    result = pd.merge(student_subject_combinations, examinations, how='left', on=['student_id', 'subject_name'])
    
    # 按学生ID、学生姓名和科目名称分组，计数出席考试次数，然后按要求排序结果
    result = result.groupby(['student_id', 'student_name', 'subject_name'], as_index=False)['attended_exams'].count()
    result = result.sort_values(['student_id', 'subject_name'])
    
    return result

mysql 解法, 执行用时: 746 ms, 内存消耗: 0 B, 提交时间: 2023-04-02 11:45:55

# Write your MySQL query statement below
SELECT stu.student_id AS 'student_id', 
    stu.student_name AS 'student_name',
    sub.subject_name AS 'subject_name',
    COUNT(exa.subject_name) AS 'attended_exams' #统计考试登记表中的
FROM 
    Students AS stu
    CROSS JOIN Subjects AS sub              #笛卡尔积
    LEFT OUTER JOIN Examinations AS exa
    ON stu.student_id = exa.student_id AND sub.subject_name = exa.subject_name
GROUP BY stu.student_id, sub.subject_name
ORDER BY stu.student_id, sub.subject_name
;

mysql 解法, 执行用时: 516 ms, 内存消耗: 0 B, 提交时间: 2023-04-02 11:45:40

# 自连接
with cte1 as
(
    select *
    from students,subjects
)


select 
    a.student_id,
    a.student_name,
    a.subject_name,
    ifnull(count(b.subject_name),0) as attended_exams 
from cte1 a
left join Examinations b
using(student_id,subject_name)
group by 1,2,3
order by 1,2,3

mysql 解法, 执行用时: 496 ms, 内存消耗: 0 B, 提交时间: 2023-04-02 11:45:17

# Write your MySQL query statement below

select ss.student_id,ss.student_name,ss.subject_name,ifnull(e.attended_exams,0) as 'attended_exams'
from

(select student_id,student_name,subject_name from Students join Subjects) ss #先链接第一个表，创造出每个学生都有对应的每行科目的记录，就出现了第一至三列的那样的表


left join

#第二个表链接起来
(select student_id,subject_name,count(student_id) as attended_exams from Examinations
group by student_id,subject_name) e #对成绩表按照id,subject和计数进行分组。
on ss.student_id=e.student_id and ss.subject_name = e.subject_name #把两个表链接起来，用leftjoin确保每一个学生后都有成绩的记录（即使为0）

group by ss.student_id,ss.subject_name
order by ss.student_id,ss.subject_name

mysql 解法, 执行用时: 681 ms, 内存消耗: 0 B, 提交时间: 2023-04-02 11:45:02

# Write your MySQL query statement below
SELECT a.student_id, a.student_name, b.subject_name, COUNT(e.subject_name) AS attended_exams
FROM Students a CROSS JOIN Subjects b
    LEFT JOIN Examinations e ON a.student_id = e.student_id AND b.subject_name = e.subject_name
GROUP BY a.student_id, b.subject_name
ORDER BY a.student_id, b.subject_name