I have two tables Person
and Salary
.
Person
:
PersonId | Name | Surname
--------------------------------
1 John Deer
2 Mark Bear
薪资
:
SId | PersonId | Date | Salary
----------------------------------------------------
1 2 2013-01-01 00:00:00.000 100
2 2 2012-01-01 00:00:00.000 90
3 2 2011-01-01 00:00:00.000 80
我要做的是,如果一个人有薪资记录,那么在结果中应该显示最新的薪资信息。如果没有薪资记录,则应将薪资信息显示为 null。就像这样...
Result
------------------------------------------------------------------------
PersonId | Name | Surname | Date | Salary
1 John Deer NULL NULL
2 Mark Bear 2013-01-01 00:00:00.000 100
我知道应该是这样的,但是由于缺乏知识,我无法实现...
SELECT
P.PersonId, P.Name, P.Surname, SL.Date, SL.Salary
FROM
PERSON P
LEFT OUTER JOIN
(SELECT TOP 1 S.PersonId, S.Date, S.Salary
FROM Salary
WHERE S.PersonId = P.PersonId ORDER BY Date DESC) SL
...) SL ON P.PersonId = SL.PersonId
的 连接条件,将这两组数据链接在一起。 - marc_s