mysql - 将我的两个查询合并为一个查询

Question

我想将这两个查询合并到一个查询中:

SELECT s.student_id, s.name,
COUNT(a.student_id) as count
FROM students s
LEFT JOIN absences a ON a.student_id = s.student_id
GROUP BY a.student_id, s.name
ORDER BY count DESC


SELECT s.unitid, s.unitname,
COUNT(a.unitid) as count
FROM subjects s
LEFT JOIN absences a ON a.unitid = s.unitid
GROUP BY s.unitid, s.unitname
ORDER BY count DESC");

这是第一个查询显示的内容:

Student ID  Student Name    ABSENCES
5       donald duck          21
3       safedin smith    13
6       ace ventura         11

这就是我希望它在组合它们时显示的内容

Student ID Unit ID  Unit Name   Student Name           ABSENCES
5       1       history     donald duck              21
3       2       maths       safedin smith               13
6       3       Crap        ace  ventura               11

因此，作为结论，第一个查询计算学生的缺勤总数，我想让它计算每个单元的学生缺勤数:

我的缺勤表包含以下列:

absence_id Ascending
student_id
date
subject
unitid

我的主题表有以下列(这意味着这是带有单位的表):

unitid
unitname

Answer 1

已编辑的查询。

SELECT s.student_id, sub.unitid, sub.unitname, s.name,
COUNT(a.student_id) as count
FROM students s
LEFT JOIN absences a ON a.student_id = s.student_id 
LEFT JOIN subjects sub ON a.unitid = sub.unitid
GROUP BY s.student_id, sub.unitid, sub.unitname, s.name
ORDER BY count DESC