SQL选择每个ID最近的记录

Question

我有两个表，第一个表如下：

     INTERNSHIP
ID|  Term
---------
1 | 2015
1 | 2014
2 | 2016
2 | 2017

第二个像这样：

       Term
Term| Term Description | End Date
---------------------------------
2014 |  Summer 2014   | 8/12014
2014 |  Summer 2014   | 8/12014
2015 |  Fall 2015     | 12/1/2015
2017 |  Spring 2017   | 4/1/2017
2017 |  Spring 2017   | 6/1/2017

我需要找到所有ID的最新期限描述。期望结果如下：

ID| Term | Term Description 
------------------
1 |  2015   |  Fall 2015   
2 |  2017   |  Spring 2017

我做了一些研究，得出了这样的解决方案：

SELECT INTERNSHIP.ID, INTERNSHIP.Term
FROM INTERNSHIP
WHERE (
  SELECT MAX(INTERNSHIP.Term)
  )
GROUP BY INTERNSHIP.ID

我已经获取了最近期限的不同ID，但是我无法将其与术语说明JOIN起来。有人能帮助我获得所需的结果吗？

- Di Tran

Answer 1

请尝试以下方法：

SELECT i.id, i.term, t.Term_Description
FROM INTERNSHIP i
INNER JOIN Term t ON t.Term = i.term
INNER JOIN (
            SELECT MAX(t.End_Date) End_Date
            FROM INTERNSHIP i
            INNER JOIN Term t ON t.Term = i.term
            GROUP BY i.ID) t1 ON t1.End_Date = t.End_Date

- Shushil Bohara

Answer 2

你的SQL版本是否支持使用窗口函数？

如果是，你可以轻松解决它，像这样：

SELECT C.ID,C.Term,C.Term_Description
FROM(
SELECT A.ID,A.TERM,B.Term_Description,Max(A.Term) OVER (PARTITION BY A.ID) AS Most_Recent_Year
FROM INTERNSHIP A INNER JOIN TERM B ON A.Term = B.Term
)C
WHERE C.Term = C.Most_Recent_Year

- Rohan Ds

Answer 3

我建议：

SELECT i.id, i.term, t.Term_Description
FROM (SELECT i.id, MAX(i.Term) as term
      FROM INTERNSHIP i
      GROUP BY i.id
     ) i JOIN
     Term t
     ON t.Term = i.term;

- Gordon Linoff

Answer 4

;WITH internships AS (
    SELECT i.id, [term] = MAX(i.term) FROM Internship i GROUP BY i.id
), terms AS (SELECT t.term, [endDate] = MAX(t.endDate) FROM Term t GROUP BY t.term)
SELECT internships.id, internships.term, t.term_Description
FROM internships
    JOIN terms ON internships.term = terms.term
    JOIN Term t ON terms.term = t.term AND terms.endDate = t.endDate

- JOE_JOE

---

根据目前的写法，你的回答不够清晰。请编辑以添加更多细节，帮助其他人理解这如何回答所提出的问题。你可以在帮助中心找到关于如何撰写好回答的更多信息。 - Community