按年分组，半年一次，四个月，季度，双月和每月一次。

Question

MySQL版本8.0SchemaSQL

CREATE TABLE IF NOT EXISTS `printer` (
  `id` INT NOT NULL,
  `name` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB;


CREATE TABLE IF NOT EXISTS `print` (
  `id` INT NOT NULL,
  `pages` INT NOT NULL,
  `copies` INT NOT NULL,
  `date` DATE NOT NULL,
  `printer_id` INT NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_print_printer_idx` (`printer_id` ASC) VISIBLE,
  CONSTRAINT `fk_print_printer`
    FOREIGN KEY (`printer_id`)
    REFERENCES `printer` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));
SET SESSION sql_mode = '';
insert into printer (id,name)
values
(1, 'printer-1'),
(2, 'printer-2'),
(3, 'printer-3');

insert into print (id,pages,copies,date,printer_id)
values
(1, 3, 2, Date('2020-01-13'), 1),
(2, 2, 1, Date('2020-01-15'), 1),
(3, 5, 2, Date('2020-01-15'), 2),
(4, 2, 1, Date('2020-02-16'), 1),
(5, 6, 2, Date('2020-02-20'), 2),
(6, 1, 5, Date('2020-02-25'), 1),
(7, 4, 1, Date('2020-07-16'), 2),
(8, 1, 2, Date('2020-07-18'), 1),
(9, 2, 1, Date('2020-10-20'), 1),
(10, 2, 1, Date('2020-11-10'), 1),
(11, 2, 1, Date('2020-12-15'), 1),
(12, 3, 3, Date('2020-12-16'), 1),
(13, 2, 1, Date('2020-12-20'), 1),
(14, 5, 2, Date('2020-12-25'), 1),
(15, 2, 2, Date('2021-01-11'), 1),
(16, 6, 1, Date('2021-01-11'), 2),
(17, 2, 5, Date('2021-01-15'), 2),
(18, 2, 1, Date('2021-01-16'), 1),
(19, 5, 7, Date('2021-01-20'), 3),
(20, 2, 1, Date('2021-01-20'), 1);

来自用户的数据(我使用变量来模拟用户发送的数据)：

SET @year_from = "2020";
SET @year_to = "2021";
SET @group = 6;

我的问题是：

SELECT YEAR(date), QUARTER(date), sum(copies*pages)
  FROM print
WHERE YEAR(date) >= @year_from AND YEAR(date) <= @year_to
GROUP BY YEAR(date), QUARTER(date)
ORDER BY YEAR(date), QUARTER(date)

结果：

YEAR(date)  QUARTER(date)   sum(copies*pages)
2020              1               37
2020              3                6
2020              4               27
2021              1               59

问题是：

1. 我也需要这条线；

    YEAR(date)  QUARTER(date)   sum(copies*pages)
    2020              1               37
 -> 2020              2                0 <-
    2020              3                6
    2020              4               27
    2021              1               59

1. 此查询不是动态的。我需要分组为1，2，3，4或6个月是动态的，因为这将由用户通知。

由于我不是sql方面的专家，所以我请求您在查询方面提供帮助。

如果有人能帮我，跟着小提琴走。

https://www.db-fiddle.com/f/gMXUGiAwVpyQemC2cvUFyf/0

我感谢你们的帮助。

ps.：如果这个查询的构造不可行，我认为解决方案可能是提取月度报告，并以编程的方式在应用程序中进行分组。如果是这样的话，请告诉我。

Answer 1

要获取每个年份/季度组合的值，您可以生成所有年份和季度的列表，然后将它们LEFT JOIN到print表中。例如，使用递归CTE：

WITH RECURSIVE years AS (
  SELECT @year_from AS year
  UNION ALL
  SELECT year + 1 
  FROM years
  WHERE year < @year_to
),
quarters AS (
  SELECT 1 AS quarter
  UNION ALL
  SELECT quarter + 1 
  FROM quarters
  WHERE quarter < 4
)
SELECT y.year, q.quarter, COALESCE(sum(copies*pages), 0)
FROM quarters q
CROSS JOIN years y
LEFT JOIN print ON QUARTER(date) = q.quarter AND YEAR(date) = y.year
GROUP BY y.year, q.quarter
ORDER BY y.year, q.quarter

输出：

year    quarter     COALESCE(sum(copies*pages), 0)
2020    1           37
2020    2           0
2020    3           6
2020    4           27
2021    1           59
2021    2           0
2021    3           0
2021    4           0

琴键演示

在使分组变量(由@group变量控制的月数)方面，我们可以将quarters CTE改为计算期间，并更改JOIN条件以计算与日期的MONTH值对应的期间：

WITH RECURSIVE years AS (
  SELECT @year_from AS year
  UNION ALL
  SELECT year + 1 
  FROM years
  WHERE year < @year_to
),
periods AS (
  SELECT 1 AS period
  UNION ALL
  SELECT period + 1 
  FROM periods
  WHERE period < 12 / @group
)
SELECT y.year, p.period, COALESCE(sum(copies*pages), 0)
FROM years y 
CROSS JOIN periods p
LEFT JOIN print ON YEAR(date) = y.year AND FLOOR((MONTH(date)-1)/@group) + 1 = p.period
GROUP BY y.year, p.period
ORDER BY y.year, p.period

对于@group = 6，这会产生以下结果：

year    period  COALESCE(sum(copies*pages), 0)
2020    1       37
2020    2       33
2021    1       59
2021    2       0

dbfiddle演示