Code
SELECT A.日期, A.部門ID, A.崗位ID, A.員工ID, A.薪資金額 AS 最后薪資, B.薪資金額 AS 之前薪資, Nz(A.薪資金額) - Nz(B.薪資金額) AS 變動(dòng)
FROM 工資變動(dòng) AS A INNER JOIN 工資變動(dòng) AS B ON (A.崗位ID = B.崗位ID) AND (A.部門ID = B.部門ID) AND (A.員工ID = B.員工ID) AND (A.日期 > B.日期)
WHERE A.日期 IN (SELECT Max(日期) FROM 工資變動(dòng) WHERE 員工ID = A.員工ID)
ORDER BY A.日期, A.部門ID, A.崗位ID, A.員工ID