MySQL 查询以生成每月的日期、项目和计数表

Question

我即将弄清楚这一点，但需要插入才能完成。我的数据集包含一月、二月和三月的记录。

enter code here

Select
  cartypes.TypeName,
  Count(cartypes.TypeName) As Count,
  COUNT(MONTH(maindata.CreatedDate) = 1) As Jan,
  COUNT(MONTH(maindata.CreatedDate) = 2) As Feb
From maindata
  Inner Join cartypes
    On maindata.TypeID = cartypes.CarType_ID
Group By cartypes.TypeName

我的输出是这样的:

TypeName                      Count      Jan    Feb 
Accreditation Audit            23        1       0
Certificate Decision            5        1       0
Certification Decision          2        1       0
Complaint About Client          1        1       0
Customer Complaint              3        1       0
Internal Audit                  5        1       0 
Internal Finding                7        1       0
Preventative Action             1        1       0
Witness Audit                   1        0       1

我尝试了几种不同的方法来尝试使每个记录的各种类型名称的计数在每个月都正确计数，但我遇到了困难。例如，Jan 应该显示认证审核的计数为 23，而 Feb 应该有 2 个认证决定和 1 个见证审核。

希望我已经说得够清楚了。

Answer 1

您当前的查询非常接近最终查询。您需要使用 CASE 表达式来获取每个 TypeName 的每个月的计数:

Select
  cartypes.TypeName,
  Count(cartypes.TypeName) As Count,
  sum(case when MONTH(maindata.CreatedDate) = 1 then 1 else 0 end) As Jan,
  sum(case when MONTH(maindata.CreatedDate) = 2 then 1 else 0 end) As Feb
From maindata
Inner Join cartypes
  On maindata.TypeID = cartypes.CarType_ID
Group By cartypes.TypeName

参见 SQL Fiddle with Demo .

或者您可以将 COUNT 与 CASE 一起使用:

Select
  cartypes.TypeName,
  Count(cartypes.TypeName) As Count,
  count(case when MONTH(maindata.CreatedDate) = 1 then cartypes.TypeName end) As Jan,
  count(case when MONTH(maindata.CreatedDate) = 2 then cartypes.TypeName end) As Feb
From maindata
Inner Join cartypes
  On maindata.TypeID = cartypes.CarType_ID
Group By cartypes.TypeName

参见 SQL Fiddle with Demo

两个查询都给出了结果:

|               TYPENAME | COUNT | JAN | FEB |
----------------------------------------------
|    Accreditation Audit |     4 |   3 |   1 |
|   Certificate Decision |     1 |   0 |   1 |
| Certification Decision |     3 |   1 |   2 |
| Complaint About Client |     3 |   3 |   0 |
|     Customer Complaint |     3 |   0 |   3 |
|         Internal Audit |     2 |   2 |   0 |
|       Internal Finding |     1 |   1 |   0 |
|    Preventative Action |     5 |   0 |   5 |
|          Witness Audit |     5 |   4 |   1 |