Sql 查询各组最新数据

列有a、b、time，查出以a分组time为最新的b的数据，组内最新

子查询，再join

SELECT
    t1.a,
    t1.b,
    t1.time
FROM
    your_table t1
INNER JOIN (
    SELECT
        a,
        MAX("time") AS max_time
    FROM
        your_table
    GROUP BY
        a
) t2 ON t1.a = t2.a AND t1.time = t2.max_time;

子查询和in

可以组合in: (a,b) in ((va1, vb1), (va2, vb2)...)

SELECT a, b, time
FROM your_table
WHERE (a, time) IN (
    SELECT a, MAX(time)
    FROM your_table
    GROUP BY a
);

使用窗口函数

RANK()：如果有多个行并列排名第一，它们都会得到相同的排名，并且下一个排名会跳过。
DENSE_RANK()：如果有多个行并列排名第一，它们都会得到相同的排名，并且下一个排名会紧随其后。

WITH RankedData AS (
    SELECT
        a,
        b,
        "time",
        RANK() OVER (PARTITION BY a ORDER BY "time" DESC) AS rnk
    FROM
        your_table
)
SELECT
    a,
    b,
    "time"
FROM
    RankedData
WHERE
    rnk = 1;