mysql - GROUP BY 和 LEFT JOIN 与同一张表中的 COUNT

Question

想按年、分型、按月排序

这是我的用户表:

id  type  uname      date_created
1    fac   a      2015-12-28 17:11:19
2    cs    b      2015-12-23 19:09:51
3    cs    c      2015-12-23 19:09:21
4    stu   d      2015-12-31 18:12:41
5    fac   e      2015-11-11 00:00:00
6    fac   f      2015-10-07 00:00:00

预期结果:

fac  stu  cs
 1    1    2 // month january
 1    0    0 // month octomber
 1    0    0 // month november

我尝试的是:

SELECT count(u1.id) as fac, count(u2.id) as stu, count(u3.id) as cs
FROM user u
left join user u1 ON u1.faculty = 'yes' AND YEAR(u1.date_created) = 2015
left join user u2 ON u2.faculty = 'no' AND YEAR(u2.date_created) = 2015
left join user u3 ON u3.faculty = 'club_student' AND YEAR(u3.date_created) = 2015
GROUP BY MONTH(u.date_created) ORDER BY MONTH(u.date_created)

给我错误的结果，例如:

fac  stu  cs
6    6    6 
6    6    6 
24   24   24

Answer 1

您需要一个 PIVOT 命令来将行转换为列。实际上，MySQL 不支持这种操作，因此我们需要手动使用 CASE WHEN(参见 SQLFiddle):

select 
  month(date_created) as month,
  count(case when faculty = 'yes' THEN 1 END) as fac,
  count(case when faculty = 'no'  THEN 1 END) as stu,
  count(case when faculty = 'club_student' THEN 1 END) as cs
from user
where 1=1
  and date_created >= STR_TO_DATE('01-01-2015','%d-%m-%Y')
  and date_created <  STR_TO_DATE('01-01-2016','%d-%m-%Y')
group by month(date_created)
order by month(date_created)

实际上，您的连接语法没有意义...您需要连接 3 次以计算每个表中的 id...对于左连接，这些表中的数据只会在存在时返回一个连接匹配，在没有匹配的情况下返回 null ...空值不计入 COUNT 聚合函数，因此您的连接语法可以简化为 INNER 连接句法。但是您甚至不需要 INNER 联接，因为您正在联接相同的表，并且您实际上没有将 FROM 表中的任何列与 JOIN< 相关联 表。

这样，您定义的过滤器可以减少为 WHERE 过滤器和 CASE WHEN 过滤器，正如我所建议的那样。