数据库的相关表声明如下:
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
教师考勤表为每节课分配一个unique numeric id
。
学生考勤表记录了该班学生的学生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
如果有人能简化这个问题,我将不胜感激。
新版本包括老师参加的所有科目
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
我使用了一个单独的子查询来计算总值。(我跳过了主题表,但很容易添加)
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