mysql - 使用 student_id 显示学生出勤情况的单一查询

Question

数据库的相关表声明如下:

students (student_id (PK), full_name, semester, ...)
subjects (subject_code (PK), subject_title, semester)
teacher_attendance (class_id (PK), subject_code(FK), teacher_id(FK), date)
student_attendance (class_id (FK), student_id (FK))

示例数据:

学生们:

student_id - mca1701
.
.
.
<a lot of irrelevant fields>
.
semester - 3

主题:

subject_code  subject_title   semester
CS006            Subject6           2
CS007            Subject7           3
CS008            Subject8           3
CS009P          Subject9 Practical  3
CS009T          Subject9 Theory     3

学生出勤率:

  class_no  student_id  

      1     mca1801
      1     mca1802
      1     mca1805
      2     *mca1701*
      2     mca1702
      3     *mca1701*
      2     mca1704
      3     mca1705
      4     mca1601
      4     mca1602
      6     mca1803
      6     mca1804
      7     *mca1701*

教师出勤率:

class_no  subject_code      teacher_id        date
    1       CS001             mca01     2019-01-14
    2       CS009P            mca01     2019-01-09
    3       CS009P            mca01     2019-01-09
    4       CS013             mca01     2019-01-17
    5       CS002             mca02     2019-01-02
    6       CS002             mca02     2019-01-10
    7       CS009T              mca02   2019-01-16
    8       CS014               mca02   2019-01-29
    9       CS014               mca02   2019-01-29
    10      CS008               mca05   2019-01-14
    11      CS003P              mca03   2019-01-15
    12      CS015P              mca03   2019-01-15
    13      CS009T              mca02   2019-01-15

教师出勤表为每个类(class)分配一个唯一数字id。

学生出勤表记录了该类学生的学号。

我想在针对特定输入 student_id 的单个查询中显示以下内容:

subject_code, subject_title, classes_attended, total_classes, % attendance

使用此查询:

SELECT subject_code, subject_title, count(class_no) as attended 
FROM 
  student_attendance 
  INNER JOIN teacher_attendance USING (class_no) 
  INNER JOIN subjects USING (subject_code) 
WHERE 
  student_id='mca1701' 
GROUP BY 
  subject_code

我能够得到 subject_code, subject_title, classes_attended :

示例操作:

subject_code    subject_title             attended  
CS009P          Subject 9 prac                  2
CS009T          Subject 9 theory                1

从这个查询中我可以获得 total_classes :

select subject_code,subject_title,count(class_no) as total_classes 
from teacher_attendance 
     INNER JOIN subjects USING (subject_code) 
WHERE semester= (SELECT semester from students where student_id='mca1701') 
GROUP BY subject_code

示例操作:

subject_code    subject_title          total_classes    
CS008              Subject8             1
CS009P             Subject9 Practical   2
CS009T              Subject9 Theory     2

我想在单个查询中获取以下数据:

要求的输出:

subject_code    subject_title          attended         total      %att
    CS009P        Subject 9 prac            2             2        100%
    CS009T        Subject 9 theory          1             2         50%
    CS007         Subject7                  0             0          0
    CS008         Subject8                  0             1          0

更新:

感谢 Joakim Danielson，得到了答案。仅在他的答案中将第二个 JOIN 更改为 LEFT JOIN，这将空行显示为 null，这就足够了。

解决方法:

SELECT u.subject_code, u.subject_title,t_count,s_count
FROM subjects u 
    JOIN students ON students.semester=u.semester 
       AND students.student_id='mca1701'
    LEFT JOIN (SELECT subject_code, COUNT(*) t_count 
       FROM teacher_attendance GROUP BY subject_code) tc
       ON tc.subject_code = u.subject_code
    LEFT JOIN (SELECT COUNT(*) s_count,subject_code 
       FROM student_attendance INNER JOIN teacher_attendance 
       USING (class_no) WHERE student_id='mca1701' 
       GROUP BY subject_code) sc 
       ON sc.subject_code = u.subject_code
GROUP BY u.subject_code, u.subject_title 

如果有人能简化这个查询，我将不胜感激。

Answer 1

新版本包括一位老师参加的所有科目

SELECT u.subject_code, u.subject_title, count(s.class_no) as attended , tc.t_count total,  CONCAT(FORMAT(100 * count(s.class_no) / tc.t_count, 0), '%') '%att'
FROM subjects u
LEFT JOIN teacher_attendance t ON t.subject_code = u.subject_code
JOIN (SELECT subject_code, COUNT(*) t_count FROM teacher_attendance GROUP BY subject_code) tc ON tc.subject_code = u.subject_code
LEFT JOIN student_attendance s ON t.class_no = s.class_no AND s.student_id = 'mca1701'
WHERE semester = 3
GROUP BY u.subject_code,  u.subject_title

---

我使用单独的子查询来计算总值(value)。 (我跳过了主题表，但很容易添加)

SELECT t.subject_code, count(t.class_no) as attended , tc.t_count total,   CONCAT(FORMAT(100 * count(t.class_no) / tc.t_count, 0), '%') %att
FROM student_attendance s
JOIN teacher_attendance t ON t.class_no = s.class_no
JOIN (SELECT subject_code, COUNT(*) t_count FROM teacher_attendance GROUP BY subject_code) tc ON tc.subject_code = t.subject_code
WHERE student_id='mca1701' 
GROUP BY subject_code