mysql - 使用 MySQL fork 值基础桶和计数

Question

我在下面提到了三个表。

表 1:

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

表2

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

表3

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 基础计数的 fork >。

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 个处于事件状态。但这是我从你的要求中了解到的。