mysql - 格式化 SQL 表删除 0 并根据年份对它们进行分组

Question

我有一个要格式化的表格。用于该表的查询是

select x.year,
round(avg(case when c.mark >= 50 and x.term = 'S1' then 1 else 0 
end)::numeric,2) as s1_pass_rate,
round(avg(case when c.mark >= 50 and x.term = 'S2' then 1 else 0 
end)::numeric,2) as s2_pass_rate
from course_enrolments c join
courses s
on c.course = s.id
join semesters x on s.semester = x.id
where s.subject in (select id from subjects where name = 'COMP SYS') and 
c.mark IS NOT NULL
group by x.year, x.term;

它生成下表:

year | s1_pass_rate | s2_pass_rate
------+--------------+--------------
2003 |         1.00 |         0.00
2003 |         0.00 |         1.00
2004 |         1.00 |         0.00
2004 |         0.00 |         0.85
2005 |         1.00 |         0.00
2005 |         0.00 |         1.00
2006 |         1.00 |         0.00
2006 |         0.00 |         1.00

我想将其格式化为:

year | s1_pass_rate | s2_pass_rate
------+--------------+--------------
03 |         1.00 |         1.00
04 |         1.00 |         0.85
05 |         1.00 |         1.00
06 |         1.00 |         1.00

不确定如何像这样对年份进行分组并删除带有 0.00 的值。请在这件事上给予我帮助。谢谢

Answer 1

您可以尝试仅按年份(而不是学期)分组，然后对两个通过率列中的每一列求和。零将在该求和中有效地无操作，为您留下您想要的非零值。尝试以下查询:

SELECT RIGHT(CONVERT(varchar(4), t.year), 2)
       SUM(t.s1_pass_rate) AS s1_pass_rate,
       SUM(t.s2_pass_rate) AS s2_pass_rate
FROM
(
    SELECT x.year,
           ROUND(AVG(CASE WHEN c.mark >= 50 AND x.term = 'S1'
                              THEN 1 ELSE 0 END)::NUMERIC, 2) AS s1_pass_rate,
           ROUND(AVG(CASE WHEN c.mark >= 50 AND x.term = 'S2'
                              THEN 1 ELSE 0 END)::NUMERIC, 2) AS s2_pass_rate
    FROM course_enrolments c
    INNER JOIN courses s
        ON c.course = s.id
    INNER JOIN semesters x
        ON s.semester = x.id
    WHERE s.subject in (SELECT id FROM subjects WHERE name = 'COMP SYS') AND
          c.mark IS NOT NULL
    GROUP BY x.year, x.term
) t
GROUP BY t.year