select   e.id as  '员工编号',E.EmpName as '姓名',case sex when 'm' then '男' when 'f' then '女' end  as '性别', borndate as '出生日期',
  case marriage when 'n' then '未婚' when 'y' then '已婚' when 'o' then '其他' end as '婚姻',hireDate as '入职日期',
 departmentName as '部门',Duty as '职务',jobID as '卡号',hireType as '员工类型',bankname as '通行区域', Huji as '户口所在地',phone as '电话',fireDate as '离职日期',
 offType as '离职原因',c.pic as '像片', CONVERT(varchar(100), bb.[Date] , 23)as '最后变动时间'
 , E.departmentCode as '部门编码', e.id
 from employee E
LEFT JOIN Department AS D ON E.departmentcode=d.departmentcode
LEFT JOIN (select   distinct id,[date] from EMP_HistoryRecord  ) as  bb on e.id=bb.id and bb.[date]=(select   max([date]) from EMP_HistoryRecord where id=e.id  )
LEFT JOIN emp_pic AS C ON E.ID=C.ID  where 'a'='a'
