c# - 如何计算选票的正确语法是什么

Question

这是我们用来计算投票学生人数的代码。

SELECT candidate,position, COUNT(studentNumber) AS 'Candidate Votes'
FROM dbvotingsystem.votes WHERE organization = 'iSITE' GROUP BY candidate
ORDER BY position;

该图像是表格中数据的示例。

om/CwvSt.png

Answer 1

如果获胜者是每组中最大候选人投票，那么秘书的获胜者应该是许多、检查以下sql:

SELECT
    position,
    substring_index(group_concat(candidate order by `Candidate Votes` desc), ',', 1) winner,
    max(`Candidate Votes`) as `Candidate Votes`
FROM (
    SELECT candidate, position, COUNT(studentNumber) AS 'Candidate Votes'
    FROM dbvotingsystem.votes
    WHERE organization = 'iSITE'
    GROUP BY candidate
) votes
GROUP BY position

结果:

|    position | winner | Candidate Votes |
|-------------|--------|-----------------|
|   President | Audrey |               7 |
|   Secretary |   Many |               8 |
|   Treasurer |  Barry |              10 |
| V.President |   Juan |               9 |

DEMO在 SQLFiddle 中。