查询不同种类最新的一条数据

1. any_value()的使用

SELECT
    t.portal_id AS portalId,
    any_value(t.cumulative_comleted) AS completed 
FROM
    (
    SELECT
        pfndo.portal_id,
        pfndo.milestones_node_date,
        pfndo.cumulative_comleted 
    FROM
        progress_file_new_detail_one pfndo
        LEFT JOIN progress_file_new pfn ON pfn.id = pfndo.fk_id 
    WHERE
        pfndo.delete_flag = 0 
        AND pfn.delete_flag = 0 
        AND pfn.process_state = '1'
        AND ref_config_id = '1707281397367967745'
    ORDER BY
        pfndo.milestones_node_date DESC
        LIMIT 1000000 
    ) t 
GROUP BY
    t.portal_id

2. 子查询使用（需要修改数据库配置，group by可以不使用聚合函数）

SELECT
    pfndo.portal_id,
    pfndo.cumulative_comleted 
FROM
    progress_file_new_detail_one pfndo
    LEFT JOIN progress_file_new pfn ON pfn.id = pfndo.fk_id
    JOIN (
    SELECT
        t.portal_id AS portalId,
        max( milestones_node_date ) AS milestones_node_date 
    FROM
        (
        SELECT
            pfndo.portal_id,
            pfndo.milestones_node_date,
            pfndo.cumulative_comleted 
        FROM
            progress_file_new_detail_one pfndo
            LEFT JOIN progress_file_new pfn ON pfn.id = pfndo.fk_id 
        WHERE
            pfndo.delete_flag = 0 
            AND pfn.delete_flag = 0 
            AND pfn.process_state = '1'
            AND ref_config_id = '1707281397367967745'
        ORDER BY
            pfndo.milestones_node_date DESC
            LIMIT 1000000 
        ) t 
    GROUP BY
        t.portal_id 
    ) t1 ON pfndo.portal_id = t1.portalId 
    AND pfndo.milestones_node_date = t1.milestones_node_date 
WHERE
    pfndo.delete_flag = 0 
    AND pfn.delete_flag = 0 
    AND pfn.process_state = '1'
    AND ref_config_id = '1707281397367967745'