根据MYSQL中的where子句在两个单独的列中显示数据

Question

我有一个必须显示学生分数的系统，我如何编写我的查询，使其显示考试分数和ca分数而不显示零，并且还能够正确地按科目ID分组，而不是重复科目名称

一个主题只能出现一次，并带有相应的CA和考试分数。

我尝试了group concat和distinct，但没有效果。

当我运行此查询时

SELECT
    DISTINCT marks.student_id,
    GROUP_CONCAT(subjects.subject_name) AS subject_name,
    subjects.id as sub_id,
    (
        CASE WHEN assessements.assessement_type = 1 THEN (
            SELECT
                ROUND(AVG(marks.mark)) as ca_average
            FROM
                marks
                INNER JOIN assessements ON assessements.id = marks.assessement_id
                INNER JOIN teaching_loads ON teaching_loads.id = marks.teaching_load_id
                INNER JOIN subjects ON subjects.id = teaching_loads.subject_id
            WHERE
                marks.student_id = 576
                AND assessements.assessement_type = 1
                AND assessements.term_id = 2
                AND subjects.id = sub_id
            ORDER BY
                subjects.id
        ) ELSE 0 END
    ) as ca,
    (
        CASE WHEN assessements.assessement_type = 2 THEN (
            SELECT
                marks.id
            FROM
                marks
                INNER JOIN assessements ON assessements.id = marks.assessement_id
                INNER JOIN teaching_loads ON teaching_loads.id = marks.teaching_load_id
                INNER JOIN subjects ON subjects.id = teaching_loads.subject_id
            WHERE
                marks.student_id = 576
                AND assessements.term_id = 2
                AND assessement_type = 2
                AND assessements.term_id = 2
                AND subject_id = sub_id
            ORDER BY
                subjects.id
        ) ELSE 0 END
    ) as exam
FROM
    marks
    INNER JOIN assessements ON assessements.id = marks.assessement_id
    INNER JOIN teaching_loads ON teaching_loads.id = marks.teaching_load_id
    INNER JOIN subjects ON subjects.id = teaching_loads.subject_id
WHERE
    marks.student_id = 576
GROUP BY
    subjects.id,
    assessements.assessement_type

这就是查询所产生的结果

​

​

我如何编写我的查询，使得它显示考试标记和ca标记而不显示零，能够正确地按主题ID分组，

像这样的东西

**Student ID | Subject Name | sub_id | ca | exam**    
   576             English      2     40    54     
   576             Geography    34    30    34 

Answer 1

不使用子查询，使用条件聚合。

SELECT
    marks.student_id,
    GROUP_CONCAT(subjects.subject_name) AS subject_name,
    subjects.id as sub_id,
    ROUND(
      AVG(
        CASE WHEN assessements.assessement_type = 1
              AND assessements.term_id = 2
             THEN marks.mark
        END
      )
    )
     AS ca,
    ROUND(
      AVG(
        CASE WHEN assessements.assessement_type = 2
              AND assessements.term_id = 2
             THEN marks.mark
        END
      )
    ) as exam
FROM
    marks
    INNER JOIN assessements ON assessements.id = marks.assessement_id
    INNER JOIN teaching_loads ON teaching_loads.id = marks.teaching_load_id
    INNER JOIN subjects ON subjects.id = teaching_loads.subject_id
WHERE
    marks.student_id = 576
GROUP BY
    marks.student_id,
    subjects.id

没有ELSE隐式返回NULL的CASE表达式没有任何WHEN子句返回TRUE。

此外，AVG()和其他聚合基本上忽略了NULL的值。这意味着NULL, 1, 2, 3的平均值是2。

综合使用这两个参数，上面的代码在一列中返回assessement_type = 1的marks.marks平均值，在另一列中返回assessement_type = 2的marks.marks平均值。