mysql - 查询和分组依据

Question

我有一些 session ，有日期、时间和考官，在这些 session 上，我安排了一名候选人。候选人必须通过考试。

我有一张这样的 table :

Exam | Meeting | Niche | Date     | Hour | Examinator | idCandidate
Math |    1    |   1   |2014-06-28|08:00 |    John    |     1
Math |    1    |   2   |2014-06-28|08:20 |    John    |     0
Math |    5    |   3   |2014-06-28|13:00 |    John    |     0
Math |    2    |   4   |2014-06-28|08:00 |    Will    |     0
Math |    2    |   5   |2014-06-28|08:20 |    Will    |     0
Math |    3    |   6   |2014-06-29|08:00 |    John    |     11
Math |    3    |   7   |2014-06-29|08:20 |    John    |     0
Math |    6    |   8   |2014-06-29|14:00 |    John    |     0
Bio  |    4    |   9   |2014-06-28|08:00 |    Bruce   |     0
Bio  |    4    |   10  |2014-06-28|08:20 |    Bruce   |     0
Bio  |    4    |   11  |2014-06-28|08:40 |    Bruce   |     0

我想要的是:

Exam |   Date   | Examinator | Nb Free Candidate
Math |2014-06-28|  John      |         2
Math |2014-06-28|  Will      |         2
Math |2014-06-29|  John      |         2
Bio  |2014-06-28|  Bruce     |         3

实际上，我想按考试、日期和考官显示免费利基( session )订单的数量。

你能帮我吗？

Answer 1

您可能正在寻找此 GROUP BY 查询:

SELECT Exam, `Date`, Examinator, COUNT(*) AS nb_free_candidate
FROM tablename
WHERE idCandidate = 0
GROUP BY Exam, `Date`, Examinator

如果您的日期列同时包含日期和时间，则应使用此选项:

SELECT Exam, DATE(`Date`), Examinator, COUNT(*) AS nb_free_candidate
FROM tablename
WHERE idCandidate = 0
GROUP BY Exam, DATE(`Date`), Examinator