mysql 查询对应条件日期最大的数据

SELECT
    * 
FROM
    data a 
WHERE
    a.del_flag = 0 
    AND a.exchange_code IN ( 'XSHG', 'XSHE' ) 
    AND EXISTS (
    SELECT
        1 
    FROM
        ( SELECT stock_code, MAX( trade_date ) AS trade_date FROM data GROUP BY stock_code ) temp 
    WHERE
        temp.stock_code = a.stock_code 
        AND temp.trade_date = a.trade_date 
    )

SELECT
    * 
FROM
    patient_state a
    JOIN ( SELECT max( state_id ) AS state_id FROM patient_state GROUP BY patid ) b ON b.state_id = a.state_id 
ORDER BY
    a.patid ASC;