mysql - 如何使用单个 SELECT 语句从表中获取多个计数

Question

这个查询看起来有逻辑错误，所以没有给出我想要的。我希望我在下面解释清楚。你能帮帮我吗？

    SELECT a.CreateBy, CreateDate,
      (SELECT COUNT(*) FROM MyTable WHERE Item1=1) as Item1Count,
      (SELECT COUNT(*) FROM MyTable WHERE Item1=2) as Item2Count,
      (SELECT COUNT(*) FROM MyTable WHERE Item1=3) as Item3Count
    FROM MyTable a;

我的表格

Id   | CreateBy | CreateDate  | Item1 | Item2 | Item3-----------------------------------------------------100  | John     | 01.06.2015  |  1    |   0   |   1101  | John     | 01.06.2015  |  1    |   1   |   1102  | Ahn      | 01.06.2015  |  0    |   1   |   0103  | Patrick  | 01.06.2015  |  1    |   1   |   0104  | John     | 02.06.2015  |  1    |   0   |   1

我想获取如下数据。

CreateBy | CreateDate | Item1Count | Item2Count | Item3Count------------------------------------------------------------John     | 01.06.2015 |     2     |      1      |    2John     | 02.06.2015 |     1     |      0      |    1Patrick  | 01.06.2015 |     1     |      1      |    0Ahn      | 01.06.2015 |     0     |      1      |    0

Answer 1

您可以使用条件求和，然后按以下方式分组

select 
CreateBy,
CreateDate,
sum(Item1=1) as Item1Count,
sum(Item2=1) as Item2Count,
sum(Item3=1) as Item3Count
from MyTable
group by CreateBy,CreateDate