Sql group by 分组取时间最新的一条数据

深藏阁楼爱情的钟 2023-10-05 18:39 147阅读 0赞

### 1.取时间最新的记录 不分组有重复(多条CreateTime一样的都是最新记录) ###

![a305237711b407e366e7266edb22fcf3.png][]

select *
    from test t
    where
    pid 
    in
    (
        select PId from Test t
        where
        time=(select max(time) from Test t1 where t1.PId=t.PId)
        group by Pid
    )
    and
    time=(select max(time) from Test t1 where t1.PId=t.PId)

![953c0ce5b5b64eef7aabb70be8b2893f.png][]

### 2.分组后取时间最新的记录 ###

SELECT max(Id)/*注意Id必须使用聚合函数Max*/ , Pid, MAX(Time) as MaxTime
    FROM Test
    GROUP BY pid

![e8ab6054c8dde6a6e3e79770c4f39e34.png][]

### 3.如果Id是uuid类型无法使用max(id)的解决办法(使用开窗函数) ###

select *
    from
    (
      select     row_number()  over(partition by [Pid] order by [Time] desc /*降序是为了where KeyId=1 (1是固定值第一条),如果升序由于不知道每组多少条where中KeyId就无法过滤了*/ ) as KeyId,* from Test   
    ) d
     where KeyId=1

![d0ff069f07918548d02cacf478cf2b31.png][]

原文：[https://www.cnblogs.com/xuejianxiyang/p/11980908.html][https_www.cnblogs.com_xuejianxiyang_p_11980908.html]

[a305237711b407e366e7266edb22fcf3.png]: https://img-blog.csdnimg.cn/img_convert/a305237711b407e366e7266edb22fcf3.png
[953c0ce5b5b64eef7aabb70be8b2893f.png]: https://img-blog.csdnimg.cn/img_convert/953c0ce5b5b64eef7aabb70be8b2893f.png
[e8ab6054c8dde6a6e3e79770c4f39e34.png]: https://img-blog.csdnimg.cn/img_convert/e8ab6054c8dde6a6e3e79770c4f39e34.png
[d0ff069f07918548d02cacf478cf2b31.png]: https://img-blog.csdnimg.cn/img_convert/d0ff069f07918548d02cacf478cf2b31.png
[https_www.cnblogs.com_xuejianxiyang_p_11980908.html]: https://www.cnblogs.com/xuejianxiyang/p/11980908.html