MySQL查询帮助，按时间分组，查看记录是否在集合中

Question

关于如何执行此操作的快速问题，我不确定它是如何工作的。基本上我有一个每天有数千行的表，我想按每一行的日期对它们进行分组。这部分很简单，但我想知道某个实例是否在该组中，例如我想知道 Paul 是否在这个日期。

   Date      Name
 ------------------
| 4/8/2011 | Jack |
| 4/8/2011 | Jack |
| 4/8/2011 | Jack |
| 4/8/2011 | Jack |
| 4/8/2011 | Jack |
| 4/8/2011 | Paul |

我有 SELECT count(*) as Total/*不确定这里发生了什么*/WHERE date = '2011-04-08' GROUP BY date

Answer 1

Select date, Count(*)
    , Max( Case When Name = 'Paul' Then 1 Else 0 End ) As HasPaul
From MyTable
Where date = '2011-04-08'
Group By date

另一种方法，您只需查看 PaulCount 是否 > 0:

Select date
    , Sum( Case When Name = 'Jack' Then 1 Else 0 End ) As JackCount
    , Sum( Case When Name = 'Paul' Then 1 Else 0 End ) As PaulCount
    , Count(*) As OverallCount
From MyTable
Where date = '2011-04-08'
Group By date