如何在另一个表中以最大id进行内部联接 [英] how to innerjoin with max id in another table

问题描述

大家好

我有两个表employee和employee status ..对于employee表中的每个雇员，在employee status表中都有条目的数目..而innerjoin为每个employee分配多行.我希望employee表中的每个employee具有最大id的employee该特定员工的状态条目..

怎么做

在此先感谢

hi all

i have two tables employee and employee status ..for each employee in employee table have number of entries in employee status table..while innerjoin more than one row comes for each employee.i want each employee in employee table inner join with maximum id employee statuis entry of that particular employee..

How to do that

Thanks in Advance

推荐答案

select employee.*,employeestatus.*
from employee with (nolock)
inner join (
            select max([status]) as [status],empid
            from employeestatus with (nolock)
            group by empid
            )
            employeestatus on employee.empid=employeestatus.empid

SELECT     employee.*,employeestatus.* 
FROM       employee INNER JOIN
                      employeestatus ON employee.empid = employeestatus.empid
WHERE     (employeestatus.statusid =
                          (SELECT     MAX(statusid)
                            FROM      employeestatus AS status_temp
                            WHERE      (empid = employee.empid)))

这将为您提供所有员工身份列.

That will get you all the columns of employee status.

这篇关于如何在另一个表中以最大id进行内部联接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！