mysql - 从同一个表 MySQL 中选择最大日期或空日期

Question

我会选择最大日期或空，但公司代码显示两次。

原表为

Employee code      Company Code     resignation date
001                     A               1/2/2013
001                     B               30/12/2014
002                     B               10/10/2000
003                     A               1/12/2014
003                     c               NULL

应该显示

EMPLOYEE_CODE      COMPANY_CODE     RESIGNATION_DATE
001                     B                30/12/2014
002                     B               10/10/2000
003                     C                NULL

但我只想为每个 EMPLOYEE_CODE 显示一条记录

EMPLOYEE_CODE      COMPANY_CODE     RESIGNATION_DATE
001                     A               1/2/2013
001                     B               30/12/2014
002                     B               10/10/2000
003                     C 

这是我的查询

SELECT EMPLOYEE_CODE , COMPANY_CODE, RESIGNATION_DATE
     FROM MT_EMPLOYEE_CONTRACT WHERE RESIGNATION_DATE IS NULL
UNION
SELECT EMPLOYEE_CODE , COMPANY_CODE, MAX(RESIGNATION_DATE) AS RESIGNATION_DATE
     FROM MT_EMPLOYEE_CONTRACT WHERE WHERE EMPLOYEE_CODE NOT IN  
     (SELECT EMPLOYEE_CODE FROM MT_EMPLOYEE_CONTRACT WHERE RESIGNATION_DATE IS NULL)   
     GROUP BY EMPLOYEE_CODE;

谁能找出问题所在吗？谢谢

Answer 1

已更新

请尝试更正后的版本:

SELECT 
    MT_EMPLOYEE_CONTRACT.EMPLOYEE_CODE , 
    MT_EMPLOYEE_CONTRACT.COMPANY_CODE, 
    MT_EMPLOYEE_CONTRACT.RESIGNATION_DATE
FROM 
    MT_EMPLOYEE_CONTRACT 
    INNER JOIN 
    (
        SELECT 
            A.EMPLOYEE_CODE , 
            MAX(A.RESIGNATION_DATE) AS MAX_RESIGNATION_DATE
        FROM 
            MT_EMPLOYEE_CONTRACT AS A
        GROUP BY 
            A.EMPLOYEE_CODE
     ) AS B
     ON MT_EMPLOYEE_CONTRACT.EMPLOYEE_CODE = B.EMPLOYEE_CODE 
        AND  MT_EMPLOYEE_CONTRACT.RESIGNATION_DATE = B.MAX_RESIGNATION_DATE;
WHERE
    NOT ISNULL(MT_EMPLOYEE_CONTRACT.RESIGNATION_DATE)

UNION 

SELECT 
    MT_EMPLOYEE_CONTRACT.EMPLOYEE_CODE , 
    MT_EMPLOYEE_CONTRACT.COMPANY_CODE, 
    MT_EMPLOYEE_CONTRACT.RESIGNATION_DATE
FROM 
    MT_EMPLOYEE_CONTRACT 
WHERE
    ISNULL(MT_EMPLOYEE_CONTRACT.RESIGNATION_DATE)