所有记录

Question

为什么我的存储过程的工作，我写了一个存储过程，可以挑选员工数据无论是对他的CURRENT工作或第一份工作或者那么这两个只存在CURRENT但我只为单身员工做到这一点。我无法弄清楚如何为表中的大量员工选择它，因为每个员工都可以有CURRENT或INITIAL或BOTH。

ALTER PROCEDURE [dbo].[ReportEmployeeProfile] 0 
AS 
BEGIN 

    SELECT 
     PersonalInfo.pk_PersonalInfo_ID, 
     PersonalInfo.PersonalInfoReligion, 
     PersonalInfo.PersonalInfoPhoneResidence, 
     PersonalInfo.PersonalInfoPhoneoffice, 
     PersonalInfo.PersonalInfoNIC, 
     PersonalInfo.PersonalInfoName, 
     PersonalInfo.PersonalInfoMobile, 
     PersonalInfo.PersonalInfoMaritalStatus, 
     PersonalInfo.PersonalInfoGender, 
     PersonalInfo.PersonalInfoFatherHusbandName, 
     CONVERT(varchar, PersonalInfo.PersonalInfoEntryDateTime, 106), 
     PersonalInfo.PersonalInfoEmailAddress, 
     PersonalInfo.PersonalInfoDomicile, 
     CONVERT(varchar, PersonalInfo.PersonalInfoDOB, 106) AS PersonalInfoDOB, 
     PersonalInfo.PersonalInfoComputerLiterate, 
     PersonalInfo.PersonalInfoAddress, 
     PersonalInfo.fk_WebUsers_PersonalInfo_UserID, 
     PersonalInfoEmployeePicture, 
     ServiceInfo.ServiceInfoInitialBPS, 
     ServiceInfo.ServiceInfoInitialDesignation 
    FROM PersonalInfo 
    INNER JOIN ServiceInfo 
     ON ServiceInfo.fk_PersonalInfo_ServiceInfo_PID = PersonalInfo.pk_PersonalInfo_ID 
    WHERE ServiceInfo.ServiceInfoJobStatus = ? 

更新： JobStatuses： 1 =初始 2 =此前 3 =电流

Answer 1

也许window function将帮助你解决你的任务。看看这个SQL代码。我已经使用ROW_NUMBER()函数来计算员工的所有工作状态并取最后一个。

SELECT T.* FROM 
    (SELECT 
      P.pk_PersonalInfo_ID, 
      P.PersonalInfoReligion, 
      P.PersonalInfoPhoneResidence, 
      P.PersonalInfoPhoneoffice, 
      P.PersonalInfoNIC, 
      P.PersonalInfoName, 
      P.PersonalInfoMobile, 
      P.PersonalInfoMaritalStatus, 
      P.PersonalInfoGender, 
      P.PersonalInfoFatherHusbandName, 
      Convert(varchar, P.PersonalInfoEntryDateTime, 106) AS PersonalInfoEntryDateTime, 
      P.PersonalInfoEmailAddress, 
      P.PersonalInfoDomicile, 
      Convert(varchar,P.PersonalInfoDOB, 106) as PersonalInfoDOB, 
      P.PersonalInfoComputerLiterate, 
      P.PersonalInfoAddress, 
      P.fk_WebUsers_PersonalInfo_UserID, 
      PersonalInfoEmployeePicture, 
      SI.ServiceInfoInitialBPS, 
      SI.ServiceInfoInitialDesignation, 
      ROW_NUMBER() OVER(PARTITION BY P.pk_personalInfo_ID ORDER BY SI.ServiceInfoJobStatus DESC) AS row_num 
    From PersonalInfo AS P 
     JOIN ServiceInfo AS SI ON SI.fk_PersonalInfo_ServiceInfo_PID= P.pk_PersonalInfo_ID) AS T 
WHERE T.row_num = 1