如果另一个表中不存在相应的值,则选择SQL值

SELECT  COALESCE (
        (
        SELECT  newValue
        FROM    history
        WHERE   field = 'Department'
                AND rowID = ID
                AND changeDate =
                (
                SELECT MAX(changedate)
                FROM   history
                WHERE  field = 'Department'
                       AND rowID = ID
                       AND changeDate <= '01/01/2009'
                )
        ), department)
FROM    employee
WHERE   id = @id

两者 Oracle MS SQL ,您也可以使用此选项:

SELECT  COALESCE(newValue, department)
FROM    (
        SELECT  e.*, h.*,
                ROW_NUMBER() OVER (PARTITION BY e.id ORDER BY changeDate) AS rn
        FROM    employee e
        LEFT OUTER JOIN
                history h
        ON      field = 'Department'
                AND rowID = ID
                AND changeDate <= '01/01/2009'
        WHERE   e.id = @id
        )
WHERE rn = 1

不过,请注意 ROWID 神谕

select iif(history.newvalue is null, employee.department, history.newvalue)
as Department
from employee left outer join history on history.RowId = employee.Id
and history.changedate < '2008-05-20'  // (i.e. given date)
and history.changedate = (select max(changedate) from history h1
where h1.RowId = history.RowId and h1.changedate <= history.changedate)