How to get data for the last N months?

Question

SQL

Hi!
There is a table containing data for each month. I want to get data for the previous N months, even if the data was not added to the table this month.

CREATE TABLE `date` (
  `year` int(11) NOT NULL,
  `month_id` int(11) NOT NULL,
  `value1` int(11) NOT NULL,
  `value2` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `month` (
  `id` int(11) NOT NULL,
  `name` varchar(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `date` (`year`,`month_id`, `value1`, `value2`) VALUES
(2019,1, 1, 1),
(2019,2, 2, 2),
(2019,3, 3, 3),
(2019,4, 4, 4),
(2019,5, 5, 5),
(2019,6, 6, 6),
(2019,8, 8, 8),
(2019,9, 9, 9),
(2019,10, 10, 10),
(2019,11, 11, 11),
(2019,12, 12, 12),
(2020,1, 1, 1),
(2020,2, 2, 2),
(2020,3, 3, 3),
(2020,4, 4, 4),
(2020,5, 5, 5),
(2020,6, 6, 6),
(2020,8, 8, 8),
(2020,9, 9, 9),
(2020,10, 10, 10),
(2020,11, 11, 11),
(2020,12, 12, 12);

INSERT INTO `month` (`id`, `name`) VALUES
(1, "Январь"),
(2, "Февраль"),
(3, "Март"),
(4, "Апрель"),
(5, "Май"),
(6, "Июнь"),
(7, "Июль"),
(8, "Август"),
(9, "Сентябрь"),
(10, "Октябрь"),
(11, "Ноябрь"),
(12, "Декабрь");

Wrote the following query, but it returns data not limited to the current month:

Select Concat(m.name, " ", d.year) as name, ifnull(d.value1,0) 
From month m left join date d On m.id = d.month_id 
order by d.year,d.month_id limit 6 OFFSET 18;

You can test here: www.sqlfiddle.com/#!9/2c1b67/23/0

Answer 1

SELECT Concat(calendar.name, " ", calendar.year) as name, ifnull(date.value1,0) AS value
 FROM (SELECT DISTINCT date.year, month.id, month.name FROM date, month) AS calendar
   LEFT JOIN date
     ON date.year = calendar.year AND date.month_id = calendar.id
 ORDER BY calendar.year, calendar.id LIMIT 6 OFFSET 18

limited to the current month … last 6

SELECT Concat(calendar.name, " ", calendar.year) as name, ifnull(date.value1,0) AS value
 FROM (SELECT DISTINCT date.year, month.id, month.name FROM date, month) AS calendar
   LEFT JOIN date
     ON date.year = calendar.year AND date.month_id = calendar.id
  WHERE CONCAT(calendar.year, '-', LPAD( calendar.id, 2, '0')) 
                 > LEFT(DATE_SUB(NOW(), INTERVAL 6 MONTH), 7)
 ORDER BY calendar.year, calendar.id LIMIT 6