你可以试着让row_number由TEACHER_ID柱和秩序的REPORTINGPERIOD，这意味着在更近的子查询日期，得到rn = 1每个数据TEACHER_ID的最大日期和JOIN上TEACHERS。
CREATE TABLE TEACHERS(
ID INT,
NAME VARCHAR(50)
);
INSERT INTO TEACHERS VALUES (1, 'Mr John Smith');
INSERT INTO TEACHERS VALUES (2, 'Ms Janet Smith');
CREATE TABLE REPORTS(
ID INT,
TEACHER_ID int,
REPORTINGPERIOD DATE,
REPORTINGTYPE varchar(100)
);
INSERT INTO REPORTS VALUES (1,1,'Jan 2017', 'Draft');
INSERT INTO REPORTS VALUES (2,1,'Feb 2017', 'Draft');
INSERT INTO REPORTS VALUES (3,2,'Jun 2018', 'Draft');
INSERT INTO REPORTS VALUES (4,2,'Jul 2018', 'Draft');
INSERT INTO REPORTS VALUES (5,1,'Mar 2017', 'Final');
：
SELECT t.NAME,
t1.REPORTINGTYPE,
t1.REPORTINGPERIOD
FROM teachers as t INNER JOIN
(
SELECT *,(SELECT COUNT(*) FROM REPORTS tt WHERE tt.TEACHER_ID = t1.TEACHER_ID and tt.REPORTINGPERIOD>=t1.REPORTINGPERIOD) rn
FROM REPORTS t1
) as t1 on t1.TEACHER_ID = t.id and rn = 1
ORDER BY t.NAME
：
| t.NAME | t1.REPORTINGTYPE | t1.REPORTINGPERIOD |
|----------------|------------------|--------------------|
| Mr John Smith | Final | Mar 2017 |
| Ms Janet Smith | Draft | Jun 2018 |
602392714
清零编程群