MySql - 动态逻辑计算器

Question

请在下面找到“MarkCompare”表

SEMESTER - PAPER - TEACHER 1 - TEACHER 2
1        - ENG   - PASS      - PASS
1        - MATH  - PASS      - FAIL
2        - ENG   - PASS      - FAIL
2        - MATH  - FAIL      - FAIL

我想计算如下逻辑

两个老师给出相同结果的次数/第一个老师两个老师的结果不同的次数之和

我正在写这样的查询

select count(*) from MarkCompare where teacher1=teacher2 where paper='ENG' / (select count(*) from MarkCompare where teacher1<>teacher2 where paper='ENG')
select count(*) from MarkCompare where teacher1=teacher2 where paper='MATH' / (select count(*) from MarkCompare where teacher1<>teacher2 where paper='MATH')

现在，将来论文的数量可能会增加或减少......我无法找到一个动态查询来运行任何数量的论文。

有没有办法不用任何过程/函数，只用一个查询就可以做到这一点

Answer 1

您可以使用 GROUP BY 子句来汇总表中列出的每篇论文的结果。

SELECT paper,
       SUM(CASE WHEN teacher1 = teacher2 THEN 1 ELSE 0 END) AS AgreeCount,
       SUM(CASE WHEN teacher1 <> teacher2 THEN 1 ELSE 0 END) AS DisagreeCount
    FROM MarkCompare
    GROUP BY paper;