用于获取属于最近日期的值的SQL连接

Question

我有一张桌子

员工

emp_id, ~10 other fields
a1
b1

emp_type

emp_id, effective_date, employee_type
a1            2/1/2011        RS
a1            9/3/2011        TU
b1            3/2/2011        RS

我正在做类似这样的事情：

select emp_id, ~10 other fields, employee_type
from employee e
inner join emp_type et
   on et.emp_id = e.emp_id
      and effective_date = (select max(effective_date) 
                            from emp_type et2 
                            where et2.emp_id = et.emp_id)

这是最有效的方式吗？它的运行速度不会太慢，但它是报表中非常常见的查询，我希望使用最有效的方法。emp_type每个员工最多有4-5条记录，但通常只有一条。大约有两万名员工。我见过一种交叉应用的解决方案，但发现它更慢。

Answer 1

我认为对子查询使用JOIN会更有效--整个结果集只计算一次，而不是每行计算一次：

select emp_id, ~10 other fields, employee_type
from employee e
inner join emp_type et
   on et.emp_id = e.emp_id
Inner join (SELECT emp_id, MAX(effective_date) effective_date
            FROM Emp_type et2
            GROUP BY Emp_id) SubQ
ON SubQ.emp_id = et.emp_id
AND SubQ.effective_date = et.effective_date

Answer 2

我建议创建一个只包含最新employee类型的视图：

CREATE VIEW active_emp_type
AS
Select a.emp_id, a.effective_date, a.employee_type
from emp_type a
where a.effective_date = (select max(effective_date) from emp_type 
                          where emp_id = a.emp_id)

(或获取相同结果视图数据的另一种方法)

然后在其他地方使用它(许多报告，等等)更简单：

select e.emp_id, ~10 other fields, t.employee_type
from employee e
join active_emp_type t on e.emp_id = t.emp_id

Answer 3

我认为这是最有效的方法。

请不要忘记将索引放在连接字段和日期时间字段上。