如何通过嵌套SELECT查询结合LEFT JOIN生成性别得分统计表

没问题，我帮你把这些内容整理成规范的Markdown格式，并且给出合并查询的解决方案：

一、现有数据库表 #

1. student表 #

存储学生基础信息：

2. period表 #

定义不同时间段的男女最大出勤人数限制：

3. attendance表 #

记录学生出勤打卡的详细信息：

二、现有男女得分查询 #

女性得分查询 #

SELECT 
    (COUNT(a.tapping_time)/2)/p.max_session_female*100 AS 'women_score' 
FROM period p 
LEFT JOIN attendance a ON p.period_id = a.period_id 
LEFT JOIN student s ON a.student_id = s.student_id  -- 修正原查询笔误：原语句中关联条件错误
WHERE s.gender = 'female' 
GROUP BY p.period_id

男性得分查询 #

SELECT 
    (COUNT(a.tapping_time)/2)/p.max_session_male*100 AS 'men_score' 
FROM period p 
LEFT JOIN attendance a ON p.period_id = a.period_id 
LEFT JOIN student s ON a.student_id = s.student_id  -- 修正原查询笔误：原语句中关联条件错误
WHERE s.gender = 'male' 
GROUP BY p.period_id

注：原查询存在关联条件笔误，a.student_id = a.student_id 应改为 a.student_id = s.student_id，否则无法正确关联学生表获取性别信息。

三、合并查询生成统一统计表 #

要生成包含时间段信息、总出勤数、男女得分的统一报表，我们可以将男女得分查询作为子查询，与period表左连接，同时计算每个时间段的总出勤数：

SELECT 
    p.period_id,
    p.`from`,
    p.`to`,
    COALESCE(total.total_att, 0) AS total_att,
    COALESCE(w.women_score, 0) AS women_score,
    COALESCE(m.men_score, 0) AS men_score
FROM period p
LEFT JOIN (
    SELECT period_id, COUNT(tapping_time) AS total_att
    FROM attendance
    GROUP BY period_id
) total ON p.period_id = total.period_id
LEFT JOIN (
    SELECT 
        p.period_id,
        ROUND((COUNT(a.tapping_time)/2)/p.max_session_female*100) AS women_score
    FROM period p 
    LEFT JOIN attendance a ON p.period_id = a.period_id 
    LEFT JOIN student s ON a.student_id = s.student_id
    WHERE s.gender = 'female' 
    GROUP BY p.period_id
) w ON p.period_id = w.period_id
LEFT JOIN (
    SELECT 
        p.period_id,
        ROUND((COUNT(a.tapping_time)/2)/p.max_session_male*100) AS men_score
    FROM period p 
    LEFT JOIN attendance a ON p.period_id = a.period_id 
    LEFT JOIN student s ON a.student_id = s.student_id
    WHERE s.gender = 'male' 
    GROUP BY p.period_id
) m ON p.period_id = m.period_id
ORDER BY p.period_id;

关键说明： #

1. 用COALESCE函数处理空值，确保无出勤记录的时间段也显示0而非NULL；
2. 用ROUND函数将得分取整，匹配期望结果的整数格式；
3. 新增总出勤数子查询，统计每个时间段的打卡总次数。

最终期望结果： #

内容的提问来源于stack exchange，提问作者codefighter