MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

SELECT @@GLOBAL.sql_mode;
SELECT @@SESSION.sql_mode;


@@GLOBAL.sql_mode


ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION




@@SESSION.sql_mode


ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION


CREATE TABLE TableA(
Dept varchar(20),	
CMonth varchar(20),	
FMonth varchar(20),	
Amount float);

INSERT INTO TableA (Dept,CMonth,FMonth,Amount)
VALUES 
('AA','2021-07','2021-07',100),
('AA','2021-07','2021-08',100),
('AA','2021-07','2021-09',100),
('AA','2021-07','2021-10',100),
('AA','2021-08','2021-08',150),
('AA','2021-08','2021-09',150),
('AA','2021-08','2021-10',150),
('AA','2021-08','2021-11',150),
('BB','2021-07','2021-07',110),
('BB','2021-07','2021-08',110),
('BB','2021-07','2021-09',110),
('BB','2021-07','2021-10',110),
('BB','2021-08','2021-08',120),
('BB','2021-08','2021-09',120),
('BB','2021-08','2021-10',120),
('BB','2021-08','2021-11',120);

SELECT *
FROM TableA


Dept
CMonth
FMonth
Amount


AA
2021-07
2021-07
100

AA
2021-07
2021-08
100

AA
2021-07
2021-09
100

AA
2021-07
2021-10
100

AA
2021-08
2021-08
150

AA
2021-08
2021-09
150

AA
2021-08
2021-10
150

AA
2021-08
2021-11
150

BB
2021-07
2021-07
110

BB
2021-07
2021-08
110

BB
2021-07
2021-09
110

BB
2021-07
2021-10
110

BB
2021-08
2021-08
120

BB
2021-08
2021-09
120

BB
2021-08
2021-10
120

BB
2021-08
2021-11
120


SELECT * FROM TableA
GROUP BY Dept,FMonth
Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'db_591547748.TableA.CMonth' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

-- MySQL 8.0
SELECT Dept,FMonth,Amount
FROM (
SELECT Dept,FMonth,Amount,
ROW_NUMBER() OVER (PARTITION BY Dept,FMonth ORDER BY CMonth DESC) AS NO
FROM TableA
) AS TEMP
WHERE NO=1
ORDER BY Dept,FMonth


Dept
FMonth
Amount


AA
2021-07
100

AA
2021-08
150

AA
2021-09
150

AA
2021-10
150

AA
2021-11
150

BB
2021-07
110

BB
2021-08
120

BB
2021-09
120

BB
2021-10
120

BB
2021-11
120


-- 非 MySQL 8.0
SELECT Z.Dept,Z.FMonth,M.Amount
FROM (
SELECT X.Dept,MAX(Y.CMonth) AS CMonth,X.FMonth
FROM TableA AS X
LEFT JOIN TableA AS Y ON Y.Dept=X.Dept AND Y.FMonth=X.FMonth
GROUP BY X.Dept,X.FMonth) AS Z
LEFT JOIN TableA M ON M.Dept=Z.Dept AND M.FMonth=Z.FMonth AND M.CMonth=Z.CMonth
ORDER BY Z.Dept,Z.FMonth


Dept
FMonth
Amount


AA
2021-07
100

AA
2021-08
150

AA
2021-09
150

AA
2021-10
150

AA
2021-11
150

BB
2021-07
110

BB
2021-08
120

BB
2021-09
120

BB
2021-10
120

BB
2021-11
120

Dept	CMonth	FMonth	Amount
AA	2021-07	2021-07	100
AA	2021-07	2021-08	100
AA	2021-07	2021-09	100
AA	2021-07	2021-10	100
AA	2021-08	2021-08	150
AA	2021-08	2021-09	150
AA	2021-08	2021-10	150
AA	2021-08	2021-11	150
BB	2021-07	2021-07	110
BB	2021-07	2021-08	110
BB	2021-07	2021-09	110
BB	2021-07	2021-10	110
BB	2021-08	2021-08	120
BB	2021-08	2021-09	120
BB	2021-08	2021-10	120
BB	2021-08	2021-11	120