本文介绍了根据状态获取最新/最新记录,如果没有在日期的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

您好。我很难从表格中获取最后/最新记录。



这些是表格的结构 []



我需要将它放在子查询中。

Hi. I am having difficulty getting the last/latest record from table.

These are the structure of the tables

[TABLE RESULT]

I need to place it in the subquery.

select EmployeeNo
,(Select PosCode from EmploymentHistory where EmployeeNo=EmpPersonalInfo.EmployeeNo) as PositionCode
,(Select Rate from EmployeeSalaryHistory where EmployeeCode=EmpPersonalInfo.EmployeeNo) as PositionCode
from  EmpPersonalInfo where EmployeeCode=902575







如果状态= 1,我可以区分两条记录中的最新记录,如果状态= 0表示表中的所有记录,如何我可以得到最新的记录(这取决于EmploymentHistory表的有效性,以及EffectivityDate-for EmployeeSalaryHistory)



除了获得TOP 1和ORDER BY DESC之外,是无论如何以大多数建议的方式做到这一点?



我尝试过:



我尝试了上面的代码,但我正在寻找更多的信息。



我试过这个,但我不能构建逻辑,我可以在这里操纵它?



选择EmpPersonalInfo.LastName,EmpPersonalInfo.FirstName,EmpPersonalInfo.MiddleName,EmpPersonalInfo.MI,

EmploymentInfo.Street,EmploymentInfo.District, EmploymentInfo。 City,EmploymentInfo.Country,EmploymentInfo.ZipCode

,position.PosTitle,tblEmployeeMainInfo.BranchCode,EmpDepartment.Department,tblEmployeeMainInfo.DivisionCode

,tblEmployeeMainInfo.AssignmentCode,EmploymentInfo.HireDate

,EmployeeSalaryHistory.EffectivityDate,EmployeeSalaryHistory.Rate

,EmployeeSalaryHistory.RateType,EmployeeSalaryHistory.Active as SalaryStatus,

EmploymentHistory.EffectivityDateFrom,EmploymentHistory.EffectivityDateTo,

EmploymentHistory.Status as EmploymentStatus

来自EmpPersonalInfo LEFT JOIN EmploymentHistory ON EmpPersonalInfo.EmployeeNo = EmploymentHistory.EmployeeNo

LEFT JOIN EmployeeSalaryHistory ON EmpPersonalInfo.EmployeeNo = EmployeeSalaryHistory.EmployeeCode

LEFT JOIN tblEmployeeMainInfo ON tblEmployeeMainInfo.EmployeeCode = EmpPersonalInfo.EmployeeNo

LEFT JOIN EmploymentInfo on EmpPersonalInfo.EmployeeNo = EmploymentInfo.EmployeeNo

LEFT JOIN职位ON Position.PoCode = EmploymentHistory.PosCode

LEFT JOIN EmpDepartment on EmpDepartment.DeptID = EmploymentHistory.DepartmentID

其中EmpPersonalInfo.EmployeeNo = 902575




I can distinguish the latest record in both records if Status=1, what if status=0 for all the records in the table, how can I get the latest record (it depends on the effectivitydatefrom- for EmploymentHistory table, and EffectivityDate -for EmployeeSalaryHistory)

Aside from getting TOP 1 and ORDER BY DESC, is there anyway to do this in most suggested way?

What I have tried:

I tried the codes above but i am seeking for additional information.

AND I TRIED THIS, but i cant build the logic, can I manipulate it here?

selectEmpPersonalInfo.LastName,EmpPersonalInfo.FirstName,EmpPersonalInfo.MiddleName,EmpPersonalInfo.MI,
EmploymentInfo.Street,EmploymentInfo.District,EmploymentInfo.City,EmploymentInfo.Country,EmploymentInfo.ZipCode
,position.PosTitle,tblEmployeeMainInfo.BranchCode,EmpDepartment.Department,tblEmployeeMainInfo.DivisionCode
,tblEmployeeMainInfo.AssignmentCode,EmploymentInfo.HireDate
,EmployeeSalaryHistory.EffectivityDate,EmployeeSalaryHistory.Rate
,EmployeeSalaryHistory.RateType,EmployeeSalaryHistory.Active as SalaryStatus,
EmploymentHistory.EffectivityDateFrom,EmploymentHistory.EffectivityDateTo,
EmploymentHistory.Status as EmploymentStatus
from EmpPersonalInfo LEFT JOIN EmploymentHistory ON EmpPersonalInfo.EmployeeNo=EmploymentHistory.EmployeeNo
LEFT JOIN EmployeeSalaryHistory ON EmpPersonalInfo.EmployeeNo = EmployeeSalaryHistory.EmployeeCode
LEFT JOIN tblEmployeeMainInfo ON tblEmployeeMainInfo.EmployeeCode=EmpPersonalInfo.EmployeeNo
LEFT JOIN EmploymentInfo on EmpPersonalInfo.EmployeeNo=EmploymentInfo.EmployeeNo
LEFT JOIN Position ON Position.PoCode = EmploymentHistory.PosCode
LEFT JOIN EmpDepartment on EmpDepartment.DeptID = EmploymentHistory.DepartmentID
where EmpPersonalInfo.EmployeeNo=902575

推荐答案

with EmploymentHistoryEx AS
(
    Select EmployeeNo, PosCode, Status, EffectivityDateFrom, EffectivityDateTo
        row_number() over(partition by EmployeeNo order by Status desc, EffectivityDateFrom desc) as HistorySeq
    from EmploymentHistory
)



您对另一个表执行相同的操作。然后在您的主查询中加入第一条记录:


You do the same for the other table. Then in your main query you join to the first record:

select 
    ...
    EmploymentHistoryEx.EffectivityDateFrom,
    EmploymentHistoryEx.EffectivityDateTo,
    EmploymentHistoryEx.Status as EmploymentStatus
    
from 
    EmpPersonalInfo 
    LEFT JOIN EmploymentHistoryEx 
        ON EmpPersonalInfo.EmployeeNo=EmploymentHistoryEx.EmployeeNo
        AND EmploymentHistoryEx.HistorySeq=1 
    ...
where EmpPersonalInfo.EmployeeNo=902575


这篇关于根据状态获取最新/最新记录,如果没有在日期的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

10-21 14:46