请教一条SQL语句

Question

select a.EMPLOYEE_CODE,a.EMPLOYEE_NAME,b.department_code from DESK_EMPLOYEE,
  (
    select employee_code,department_code from members_of_department,
      (
        select max(update_time) newest
          from members_of_department
          group by employee_code
      )
      where update_time=newest
  ) b
  where a.employee_code=b.employee_code;

Answer 1

select a.EMPLOYEE_CODE,a.EMPLOYEE_NAME,b.department_code from DESK_EMPLOYEE a,
上个贴少了个别名

Answer 2

不对:(，只取出来了一个employee的信息，
而且这条记录重复了n次

Answer 3

select desk_employee,members_of_department from desk_employee,members_of_department where desk_employee.EMPLOYEE_CODE=desk_employee.EMPLOYEE_CODE and desk_employee.update_time
 in(select Max(desk_employee.update_time) from desk_employee group by department_code)  group by desk_employee,members_of_department

Answer 4

select a.employee_code,a.EMPLOYEE_NAME,b.department_code from DESK_EMPLOYEE,
    (select distinct employee_code,FIRST_VALUE(department_code) OVER (PARTITION BY employee_code  ORDER BY update_time DESC) department_code
    from members_of_department) b
where a.employee_code = b.employee_code

Answer 5

把 xjlsj(lsj) 的sql语句稍微改了一下，现在可以取出来了，但是又有一个新问题，
就是有多个相同的update_time的时候，还是会取出来多个记录:（,不知道该如何解决。
我的sql语句如下：
select a.EMPLOYEE_CODE,a.EMPLOYEE_NAME,b.department_code from desk_employee a,
(select Max(members_of_department.LAST_UPDATE_DATETIME),department_code,employee_code 
from members_of_department group by department_code,employee_code) b
 where a.EMPLOYEE_CODE=b.EMPLOYEE_CODE

Answer 6

duanzilin的语句可以的，非常感谢。