使用MySQL将值bucket和count分叉

Question

我在下面提到了三个表。

Table1：

ID         Ref_ID          category_id    Date
II-1       xrt-11-gt       1              2019-01-01 15:34:18
II-2       xtt-10-xt       1              2019-01-05 17:14:20
II-3       xyt-09-yu       2              2019-02-04 11:04:12
II-4       xet-12-ct       2              2019-02-01 12:33:14

Table2

Ref_ID2       Value
xrt-11-gt     150
xrt-11-gt     175
xrt-11-gt     165
xrt-11-gt     168
xtt-10-xt     200
xtt-10-xt     45
xyt-09-yu     34
xet-12-ct     78

Table3

ref        type
1          Active
2          InActive
3          Hold

我有一个问题，比如

select a.ID,a.Date,b.Value,c.type from Table1 a
left join Table2 b on a.Ref_ID=b.Ref_ID2
left join Table3 c on a.category_id=c.ref;

这将返回下面提到的输出。

ID      Value      Type         Date
II-1    150        Active       2019-01-01 15:34:18
II-1    175        Active       2019-01-01 15:34:18
II-1    165        Active       2019-01-01 15:34:18
II-1    168        Active       2019-01-01 15:34:18
II-2    200        InActive     2019-01-05 17:14:20
II-2    45         InActive     2019-01-05 17:14:20
II-3    34         InActive     2019-02-04 11:04:12
II-4    78         InActive     2019-02-01 12:33:14

我需要在MySQL中将上面的输出转换成下面提到的格式。

其中，1-3和3-5是ID计数在其根据Type落入的桶上的分叉。

Month       Total      Active  1-3   3-5   InActive   1-3   3-5  Hold  1-3  3-5
Jan-19       6           2      1     1       0         0    0     0    0    0
Feb-19       2           0      0     0       2         2    0     0    0    0

Answer 1

这就是你要找的吗：

SELECT
    DATE_FORMAT(date, '%b-%y') month,
    COUNT(*) total,
    SUM(type = 'Active') active,
    CASE WHEN SUM(type = 'Active') BETWEEN 1 AND 3 THEN SUM(type = 'Active') ELSE 0 END `1-3`,
    CASE WHEN SUM(type = 'Active') BETWEEN 4 AND 5 THEN SUM(type = 'Active') ELSE 0 END `4-5`,
    SUM(type = 'InActive') inactive,
    CASE WHEN SUM(type = 'InActive') BETWEEN 1 AND 3 THEN SUM(type = 'InActive') ELSE 0 END `1-3`,
    CASE WHEN SUM(type = 'InActive') BETWEEN 4 AND 5 THEN SUM(type = 'InActive') ELSE 0 END `4-5`,
    SUM(type = 'Hold') hold,
    CASE WHEN SUM(type = 'Hold') BETWEEN 1 AND 3 THEN SUM(type = 'Hold') ELSE 0 END `1-3`,
    CASE WHEN SUM(type = 'Hold') BETWEEN 4 AND 5 THEN SUM(type = 'Hold') ELSE 0 END `4-5`
FROM 
    Table1 a
    LEFT JOIN Table2 b on a.Ref_ID=b.Ref_ID2
    LEFT JOIN Table3 c on a.category_id=c.ref
GROUP BY DATE_FORMAT(date, '%b-%y');

请注意，这不会完全返回您在预期输出中显示的内容。特别是，在第一个记录中会有一些不同，即显示1月份的聚合数据:该月的总数将是6，其中4是活动的。但这是我从你的需求中理解的。