mysql - 在 SQL 表之间加入

Question

我有5张 table

1. SCHOOL[id(bigInt, primary), name(varchar)]
2. SELECTED_INDICATOR[id(bigInt, primary), school_id(bigint)]
3. TEACHER[id(bigint, primary), indicator_id(bigInt), attendance_id(int)]
4. STUDENT[id(bigint, primary), indicator_id(bigInt), attendance_id(int)]
5. MIDDAY_MEAL[id(bigint,primary), indicator_id(bigint), served(boolean), consumed_number(int)]

在TEACHER表中，attendance_id可以有值:1或2或3。同样，在STUDENT表中，attendance_id的值可以是:1或2。

我必须根据 SELECTED_INDICATOR id 生成报告，格式如下:

学校编号 |学校名称 |总教师 |教师_1 |老师_2 |老师_3 |总学生 |学生_1 |学生_2 |服务 |消费数量

为此我尝试过:

select A.id, A.school_id, SC.name, 
 SUM(CASE WHEN T.attendance_id IN (1,2,3) THEN 1 ELSE 0 END) as TOTAL_TEACHER,
 SUM(CASE WHEN T.attendance_id IN (1) THEN 1 ELSE 0 END) as TEACHERS_1,
 SUM(CASE WHEN T.attendance_id IN (2) THEN 1 ELSE 0 END) as TEACHERS_2,
 SUM(CASE WHEN T.attendance_id IN (3) THEN 1 ELSE 0 END) as TEACHERS_3,
 SUM(CASE WHEN S.attendance_id IN (1,2) THEN 1 ELSE 0 END) as TOTAL_STUDENT,
 SUM(CASE WHEN S.attendance_id IN (1) THEN 1 ELSE 0 END) as STUDENTS_1,
 SUM(CASE WHEN S.attendance_id IN (2) THEN 1 ELSE 0 END) as STUDENTS_2,
 M.served, M.consumed_number
from SELECTED_INDICATOR A
join SCHOOL SC on A.school_id = SC.id
join TEACHER T on A.id = T.indicator_id
join STUDENT S on A.id = S.indicator_id
join MIDDAY_MEAL M on A.id = M.indicator_id
WHERE A.STATUS = 'COMPLETED' group by A.id;

当我一次加入一个带有 SELECTED_INDICATOR 的 TEACHER 或 STUDENT 时，它会为我提供正确的数据。但是当我在上面的查询中使用 SELECTED_INDICATOR 加入 TEACHER 和 STUDENT 时，我得到了与教师和学生相关的大量数字。

我的查询有什么问题？请帮助更正它，或提供任何替代查询。

Answer 1

尝试使用具有使用不同值的选项的 COUNT()。问题是表格会乘以结果。

    select A.id, A.school_id, SC.name, 
     COUNT(DISTINCT CASE WHEN T.attendance_id IN (1,2,3) THEN t.TeacherID  END) as TOTAL_TEACHER,
     COUNT(DISTINCT CASE WHEN T.attendance_id IN (1) THEN t.TeacherID END) as TEACHERS_1,
    ....
    FROM ....