SQL Join 获取值属于最近的日期 [英] SQL Join to get value belong with most recent date

问题描述

我有一张桌子

员工

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 条记录，但通常只有一条.大约有20,000名员工.我看过一个交叉应用的解决方案，但发现速度较慢.

Is this the most efficient way of doing this? It doesn't run too slow, but it's a very common query in reports and I want to have the most efficient method. The emp_type has at most 4-5 records per employee, but usually just one. There are about 20,000 employees. I've seen a cross-apply solution but found that to be slower.

推荐答案

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

I think a JOIN on a subquery would be more efficient - the whole result set is calculated once instead of once per row:

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

这篇关于SQL Join 获取值属于最近的日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！