MySQL CrossTab和删除重复项

Question

我在MySQL有一个表格，每年都有学生注册。我想提出一个问题，将显示nnn学生在学年结束。我很快就完成了这个查询，但让我头疼的是，数据库中有一些重复的条目，我需要从总数中拒绝这些条目。

到目前为止，这是我的疑问。而且，就像我说的，我知道它允许重复的值在几年内。

SELECT COUNT(YEAR(birthdate)) as YearCount, YEAR(birthdate) as BirthYear, AcadYearEnding 
FROM Enrollment 
GROUP BY AcadYearEnding 
ORDER BY AcadYearEnding, BirthYear

结果看起来很像(如果我添加了WHERE子句来限制特定的学生)：

YearCount   BirthYear   AcadYearEnding
1             2007          2012
2             2007          2013
1             2007          2014
1             2007          2015

这是生成上述数据的WHERE子句的查询.但我最终将删除WHERE子句，以显示所有学生，但没有任何学生重复的条目。

SELECT COUNT(YEAR(birthdate)) as YearCount, YEAR(birthdate) as BirthYear, AcadYearEnding 
FROM Enrollment 
WHERE StudentId=16183
GROUP BY AcadYearEnding 
ORDER BY AcadYearEnding, BirthYear

考虑到我的where子句仅限于一个学生，我知道2013年的条目是数据库中额外一行的结果(对数据进行清理不是一个选项，我们需要在查询中限制它)。

我试着做了个分选给我一个独特的学生证列表.但在2013年，它发现学生身份证是两次，所以它仍然计算了两次。

以下是生成不正确结果的原始数据，因为2013年有2条记录：

RecId   StudentId   Birthdate   AcadYearEnding
39885   16183       11/29/2005  2012
33768   16183       11/29/2005  2013
31020   16183       11/29/2005  2013
59508   16183       11/29/2005  2014
64054   16183       11/29/2005  2015

RecId是表的唯一标识符。

我可能需要以下几点(但我希望有人能确认)：

SELECT COUNT(YEAR(birthdate)) as YearCount, YEAR(birthdate) as BirthYear, AcadYearEnding 
FROM (SELECT DISTINCT(StudentId), birthdate, AcadYearEnding From Test) As Ed
GROUP BY AcadYearEnding 
ORDER BY AcadYearEnding, BirthYear

Answer 1

所以我在问题的最下面提出的(可能的)答案是接近.但不太对。实际的答案是：

SELECT (COUNT(YEAR(Birthdate)) As YearCount, Year(Birthdate) As BirthYear, AcadYearEnding
From (SELECT DISTINCT(StudentId), AcadYearEnding, Birthdate From Enrollment) As Ed
GROUP BY BirthYear, AcadYearEnding
ORDER BY AcadYearEnding, BirthYear

我已经统计了生日..。它可以，也许应该是StudentId，但不管是哪种方式，计数都是一样的。

更多的测试，但我很感激你能帮我这么近。

Answer 2

你只要count(distinct)，我想：

SELECT COUNT(DISTINCT StudentId) as YearCount, YEAR(birthdate) as BirthYear, AcadYearEnding 
FROM Enrollment 
WHERE StudentId = 100510230
GROUP BY AcadYearEnding 
ORDER BY AcadYearEnding, BirthYear;

当然，在没有WHERE子句的情况下，这也是可行的：

SELECT COUNT(DISTINCT StudentId) as YearCount, YEAR(birthdate) as BirthYear, AcadYearEnding 
FROM Enrollment 
GROUP BY AcadYearEnding 
ORDER BY AcadYearEnding, BirthYear;

Answer 3

SELECT
  COUNT(DISTINCT StudentId) AS YearCount,
  YEAR(birthdate) AS BirthYear,
  AcadYearEnding
FROM (SELECT
  StudentId,
  AcadYearEnding,
  BirthYear
FROM Enrollment
GROUP BY StudentId,
         AcadYearEnding) AS t
GROUP BY AcadYearEnding
ORDER BY AcadYearEnding, BirthYear;

正如您所指出的，您可以通过使用限制来测试这一点。不需要使用where子句，因为限制将为您提供更广泛的查询测试范围。(试着使用极限10;)。当然，这取决于您有多少特定年份的数据。