代码之家 › 专栏 › 技术社区 › Amiel Echizen

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

mysql php

1

Amiel Echizen · 技术社区 · 6 年前

我没能在几个月内把日期后面的行合并起来。我做了如下的查询,但是结果并不像我预期的那样。

我使用myphpadmin在那里执行SQL,同时保存我的数据库。我尝试使用pivot for 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
--------------------------------------------------------

1 回复 | 直到 6 年前

1

0

Nick SamSmith1986 6 年前

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