如何将两个查询的结果连接到一个按YEAR和MONTH分组的表中

Question

我有两个表，materials_students和components_students。它们都有一个finished_at列。material_student具有component_student_id列。

我需要计算components_students和materials_student的数量(其中finished_at id不为NULL(，从finished_at中提取月份和年份，按月份和年份对结果进行分组，并将其绘制在一个表中，如下所示：

| Materials  | Components |  Month |  Year
---------------------------------------------  
|   45             3            1     2019
|   37             6            2     2019
|   63             8            3     2019 

我知道如何只对一个表执行此操作，但不知道如何将结果连接到一个表中。

在下面找到我是如何为一张桌子做的：

FROM materials_students
LEFT JOIN students ON materials_students.student_id = students.id
LEFT JOIN company_profiles ON students.company_profile_id = company_profiles.id
LEFT JOIN companies ON company_profiles.company_id = companies.id
WHERE materials_students.finished_at IS NOT NULL
GROUP BY YEAR, MONTH
ORDER BY YEAR, MONTH

谢谢！

Answer 1

最好的方法是为每个案例组装一个子查询，然后将它们连接起来。

select
ISNULL(M.yy, C.yy) [yy],
ISNULL(M.mm, C.mm) [mm], 
ISNULL(number_material_students, 0) [number_material_students],
ISNULL(number_components_students, 0) [number_component_students]
from (
SELECT
year(materials_students.finished_at) yy, 
month(materials_students.finished_at) mm,
count(*) number_material_students
FROM materials_students
LEFT JOIN students ON materials_students.student_id = students.id
LEFT JOIN company_profiles ON students.company_profile_id = company_profiles.id
LEFT JOIN companies ON company_profiles.company_id = companies.id
WHERE materials_students.finished_at IS NOT NULL
GROUP BY year(materials_students.finished_at), month(materials_students.finished_at)
) M
full outer join (
SELECT
year(components_students.finished_at) yy, 
month(components_students.finished_at) mm,
count(*) number_material_students
FROM components_students
LEFT JOIN students ON components_students.student_id = students.id
LEFT JOIN company_profiles ON students.company_profile_id = company_profiles.id
LEFT JOIN companies ON company_profiles.company_id = companies.id
WHERE components_students.finished_at IS NOT NULL
GROUP BY year(materials_students.finished_at), month(materials_students.finished_at)
) C
ON C.yy = M.yy AND C.mm = M.mm
ORDER BY 1, 2

我不得不在子查询之间制作一个FULL OUTER JOIN，因为可能有一些年/月只出现在材料上，而没有出现在组件上，反之亦然。

为了检索年份，我使用ISNULL()函数，因此，如果年份没有从材料子查询中填写，我将从组件子查询中使用。类似的推理适用于所有其他生成的列。