如何按月统计mysql的三列？

Question

该表有 3 列过滤器（filter1、filter2、filter3）和一列日期，您需要拉出每个过滤器并按月计算它们的数量 有一个过滤器的工作请求，但如何按月计数正确拉出 3 个过滤器？

SELECT * 
FROM ( SELECT filter1, COUNT() AS 'Количество' 
       FROM   mosedo.doc_rows_og 
       WHERE  filter1 IS NOT NULL 
       GROUP BY filter1 
     ) AS a LEFT JOIN ( 
       SELECT filter1, COUNT() AS 'January ' 
       FROM   mosedo.doc_rows_og 
       WHERE  data_reg LIKE '%-01-%' 
       AND    filter1 IS NOT NULL 
       GROUP BY filter1
     ) as b USING(filter1) LEFT JOIN (
       SELECT filter1, COUNT(*) AS 'February ' 
       FROM   mosedo.doc_rows_og 
       WHERE  data_reg LIKE '%-02-%' 
       AND    filter1 IS NOT NULL 
       GROUP BY filter1
     ) as c using(filter1)

结果：

DDL 和DML

db<>fiddle 此处< /em>

CREATE TABLE `doc_rows_og` (
  `number_doc` varchar(250) NOT NULL,
  `data_reg` longtext,
  `komy` longtext,
  `ot_kogo` longtext,
  `soderjanie` longtext,
  `number_sogol` longtext,
  `number_otv` longtext,
  `srok_sam` longtext,
  `srok_isp` longtext,
  `fio_isp` varchar(250) NOT NULL,
  `status_` longtext,
  `porych` longtext,
  `vid` longtext,
  `istochink` longtext,
  `ot_kuda` longtext,
  `nomer` longtext,
  `status` longtext,
  `control` longtext,
  `name_object` longtext,
  `kto` longtext,
  `rezultat` longtext,
  `filter1` longtext,
  `filter2` longtext,
  `filter3` longtext,
  `dostovernost` longtext,
  `sujet` longtext,
  `opisanie` longtext,
  `prim` longtext,
  `type_object` longtext,
  `type` longtext,
  PRIMARY KEY (`number_doc`,`fio_isp`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

INSERT INTO `mosedo`.`doc_rows_og` (`number_doc`, `data_reg`, `komy`, `ot_kogo`, `soderjanie`, `number_sogol`, `number_otv`, `srok_sam`, `srok_isp`, `fio_isp`, `status_`, `porych`, `filter1`, `filter2`, `filter3`) VALUES ('1', '2022-01-25', 'test', 'test', 'test', 'test', 'test', 'test', 'test', 'test', 'test', 'test', 'filterA', 'filterB', 'filterC');
INSERT INTO `mosedo`.`doc_rows_og` (`number_doc`, `data_reg`, `komy`, `ot_kogo`, `soderjanie`, `number_sogol`, `number_otv`, `srok_sam`, `srok_isp`, `fio_isp`, `status_`, `porych`, `filter2`, `filter3`) VALUES ('2', '2022-02-25', 'test', 'test', 'test', 'test', 'test', 'test', 'test', 'test', 'test', 'test', 'filterC', 'filterA');
INSERT INTO `mosedo`.`doc_rows_og` (`number_doc`, `data_reg`, `komy`, `ot_kogo`, `soderjanie`, `number_sogol`, `number_otv`, `srok_sam`, `srok_isp`, `fio_isp`, `status_`, `porych`, `filter1`, `filter2`, `filter3`) VALUES ('3', '2022-01-25', 'test', 'test', 'test', 'test', 'test', 'test', 'test', 'test', 'test', 'test', 'filterB', 'filterA', '');
INSERT INTO `mosedo`.`doc_rows_og` (`number_doc`, `data_reg`, `komy`, `ot_kogo`, `soderjanie`, `number_sogol`, `number_otv`, `srok_sam`, `srok_isp`, `fio_isp`, `status_`, `porych`, `filter1`, `filter2`, `filter3`) VALUES ('4', '2022-02-25', 'test', 'test', 'test', 'test', 'test', 'test', 'test', 'test', 'test', 'test', 'filterB', 'filterC', 'filterA');

• 第一列 - 过滤器A、过滤器B、过滤器C；
• 第二列 - 对第一列中的每个值进行计数，其中 data_reg like '%-01-%'；
• 第三列 - 对第一列中的每个值进行计数，其中 data_reg like '%-02-%'

Answer 1

如果您尝试将所有filter1、filter2 和filter3 值合并到单个列中，则需要UNION ALL 而不是JOIN。然后使用条件 SUM() 按月计算行数。

请注意，该示例使用 STR_TO_DATE() 因为 data_reg 列将日期存储为字符串。理想情况下，您应该将该列的数据类型更改为 DATE，以便您可以更好地利用可用的 日期函数。

SELECT FilterValue
      , COUNT(*) AS TotalRows
      , SUM(CASE WHEN month(str_to_date(data_reg, '%Y-%m-%d')) = 1 THEN 1 ELSE 0 END) AS January
      , SUM(CASE WHEN month(str_to_date(data_reg, '%Y-%m-%d')) = 2 THEN 1 ELSE 0 END) AS February
FROM (
         SELECT filter1 AS FilterValue, data_reg
         FROM   doc_rows_og
         WHERE  filter1 IS NOT NULL 
         UNION ALL 
         SELECT filter2 AS FilterValue, data_reg
         FROM   doc_rows_og
         WHERE  filter2 IS NOT NULL 
         UNION ALL
         SELECT filter3 AS FilterValue, data_reg
         FROM   doc_rows_og
         WHERE  filter3 IS NOT NULL 
) t
WHERE FilterValue <> ''
GROUP BY FilterValue
;

结果：

过滤值 |总行数 |一月 |二月
:---------- | --------： | ------: | --------：
过滤器A | 4 | 2 | 2
过滤器B | 3 | 2 | 1
过滤器C | 3 | 1 | 2

db<>fiddle 此处