你可以试着让
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');
查询1 ：
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 = 1ORDER BY t.NAME
结果 ：
| t.NAME | t1.REPORTINGTYPE | t1.REPORTINGPERIOD ||----------------|------------------|--------------------|| Mr John Smith | Final |Mar 2017 || Ms Janet Smith | Draft |Jun 2018 |
