[SQL] 找出最新日期, 最舊日期, 最大值, 最小值

select e.*,f.姓名,f.部門,f.name from salary_month as e left join (select c.staff_sn,c.zhFname+c.zhName as 姓名,c.部門,d.name from employee as c left join (select a.* from employeePosition as a ,(select staff_sn,max(createDate) as createDate from employeePosition GROUP BY staff_sn) as b where a.staff_sn = b.staff_sn and a.createDate = b.createDate) as d on d.staff_sn = c.staff_sn) as f on e.emp_sn = f.staff_sn where e.日期 = '201306';

加入(身份2)
select e.*,f.姓名,f.部門,f.name,z.身份2 from salary_month as e left join (select a.emp_sn,a.身份2  from salary_basic as a, (select emp_sn,max(調整日期) as 調整日期 from salary_basic GROUP BY emp_sn) as b where a.emp_sn = b.emp_sn and a.調整日期 = b.調整日期 and a.身份2=1) as z on z.emp_sn = e.emp_sn  left join (select c.staff_sn,c.zhFname+c.zhName as 姓名,c.部門,d.name from employee as c left join (select a.* from employeePosition as a ,(select staff_sn,max(createDate) as createDate from employeePosition GROUP BY staff_sn) as b where a.staff_sn = b.staff_sn and a.createDate = b.createDate) as d on d.staff_sn = c.staff_sn) as f on e.emp_sn = f.staff_sn where e.日期 = '201306' and z.身份2 = 1;

參考：
[SQL] 找出職員最新一筆薪資調整紀錄並取得其職務名稱與任職日
SQL_找出最新日期、最舊日期、最大值、最小值
SQL的使用技巧_MAX()、MIN()與Group By、HAVING
請教一個麻煩的 SQL 語法？要找出每個會員的最新一筆訂單資料
請問如何在多個客戶下,找出每一個客戶跟我交易最近的日期
只抓取日期最大的單據資料
SELECT only the rows with MAX(DATE)
Need a little help, SQL Server 2000, having and max(date)
SQL语句练习实例之一——找出最近的两次晋升日期与工资额
Linq小技巧:日期處理
多個各自count之後還要join成一張表