mysql -通过计数选择行组？

Question

给出一个显示组织成员获得的奖励的表格，我试图列出在给定日期后获得三个或更多奖励的所有成员，并列出这些成员获得的所有奖励。我有一个查询可以正确地列出成员，但它只显示每个成员一行，并且我正在尝试查看他们的所有奖励。

在表格中，任何成员获得的每个奖励(“奖励”)都有一行，其中包含奖励类型和获得奖励的日期("Awarddate")。

这就是我所拥有的：

SELECT Membername, Award, Awarddate,COUNT(Membername) 
FROM awards 
WHERE Awarddate > '2011-06-30' 
GROUP BY Membername HAVING COUNT(Membername) > 2 

有什么建议吗？

Answer 1

您可以使用GROUP_CONCAT()聚合函数：

SELECT Membername
     , COUNT(*)                                    AS NumberOfAwards
     , GROUP_CONCAT(Award ORDER BY Awarddate)      AS Awards
     , GROUP_CONCAT(Awarddate ORDER BY Awarddate)  AS AwardDates
FROM awards 
WHERE Awarddate > '2011-06-30' 
GROUP BY Membername 
HAVING COUNT(*) > 2 

Answer 2

SELECT a.Membername, a.Award, a.Awarddate, Awardcount.Awards
FROM awards a
JOIN
  ( SELECT Membername, COUNT(*) AS Awards
    FROM awards
    WHERE Awarddate > '2011-06-30' 
    GROUP BY Membername 
    HAVING Awards > 2 ) Awardcount
ON a.Membername = Awardcount.Membername

编辑:要仅列出在给定日期之后获得的奖励，请按照ypercube的建议在此查询中再添加一行：

WHERE a.Awarddate > '2011-06-30'