By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select version();
| version() |
|---|
| 8.0.30 |
create table t
(iD int auto_increment primary key, dt date);
insert into t (dt) values
('2023-01-01'),('2023-01-01'),
('2023-02-01'),
('2023-03-01'),('2023-03-02'),('2023-03-03')
;
create table t1
with cte as
(
select distinct cast(date_format(t.dt,'%Y%m') as unsigned)ym
from t)
select * from cte
join t on cast(date_format(t.dt,'%Y%m') as unsigned) <= cte.ym ;
Records: 6 Duplicates: 0 Warnings: 0
Records: 11 Duplicates: 0 Warnings: 0
select * from t1;
| ym | iD | dt |
|---|---|---|
| 202301 | 2 | 2023-01-01 |
| 202301 | 1 | 2023-01-01 |
| 202302 | 3 | 2023-02-01 |
| 202302 | 2 | 2023-01-01 |
| 202302 | 1 | 2023-01-01 |
| 202303 | 6 | 2023-03-03 |
| 202303 | 5 | 2023-03-02 |
| 202303 | 4 | 2023-03-01 |
| 202303 | 3 | 2023-02-01 |
| 202303 | 2 | 2023-01-01 |
| 202303 | 1 | 2023-01-01 |
show create table t1;
| Table | Create Table |
|---|---|
| t1 | CREATE TABLE `t1` ( `ym` bigint unsigned DEFAULT NULL, `iD` int NOT NULL DEFAULT '0', `dt` date DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |