php - 将行与月份中的日期合并在一起

Question

我无法合并日期(以月为单位)后面的行。我做了如下查询，但结果并不像我预期的那样。

当我保存数据库时，我使用 myphpadmin 来执行 sql。我尝试对 php 使用数据透视表，但它显示失败。

SELECT MONTH(Date) as Months ,Code='J01' as A,Code='J02' as B,Code='J03' as C, COUNT(Date) as Total FROM Table_a WHERE Code IN('J01','J02','J03') AND YEAR(Date)='2018' GROUP BY Code, Months 

由于上述查询的结果如下；

-------------------------------------------------------
Months |   A    |        B        |    C       | Total 
-------------------------------------------------------
1          1             0             0          12
2          1             0             0          15
3          1             0             0          10
4          1             0             0           4
5          1             0             0          11
6          1             0             0           8
7          1             0             0           3
8          1             0             0          10
9          1             0             0          15
10         1             0             0          12
11         1             0             0          10
12         1             0             0          25
8          0             1             0           5
9          0             1             0          10
10         0             1             0          15
11         0             1             0          20
12         0             1             0          25
8          0             0             1          10
9          0             0             1           9
10         0             0             1          13
11         0             0             1          17
12         0             0             1          18
--------------------------------------------------------

我希望它像下面这样:

-------------------------------------------------------
Months |   A    |        B        |    C       | Total  
-------------------------------------------------------
1         12             0             0          12
2         15             0             0          15
3         10             0             0          10
4          4             0             0           4
5         11             0             0          11
6          8             0             0           8
7          3             0             0           3
8         10             5            10          25
9         15            10             9          34
10        12            15            13          50
11        10            20            17          47
12        25            25            18          68
--------------------------------------------------------

Answer 1

您不应按 Code 进行分组，并且需要对每种 Code 类型的出现次数进行SUM。试试这个:

SELECT MONTH(Date) as Months,
       SUM(Code='J01') as A,
       SUM(Code='J02') as B,
       SUM(Code='J03') as C, 
       COUNT(Date) as Total
FROM Table_a 
WHERE Code IN('J01','J02','J03') AND YEAR(Date)='2018' 
GROUP BY Months