pgsql 查询分组之后每组最新的数据

明天接着卷

已于 2023-02-28 09:28:25 修改

阅读量1.2k

点赞数

文章标签：数据库 postgresql

于 2023-02-03 17:09:58 首次发布

本文链接：https://blog.csdn.net/m0_64081550/article/details/128871747

版权

将工单为 230106103710336200 的数据按handle_unit_guid, handle_unit分组，查询每组最新的一条数据

方法一

SELECT * FROM (
	SELECT handle_unit_guid, row_number() over (partition by handle_unit_guid, handle_unit order by accept_time desc), guid FROM hl_taskhotline WHERE  id='230106103710336200' 
) AS de_dump WHERE row_number = 1

方法二

SELECT 
	app.handle_unit_guid,
	app.handle_unit,
	(SELECT "guid" FROM hl_taskhotline AS temp1 WHERE temp1.handle_unit_guid = app.handle_unit_guid AND temp1.handle_unit = app.handle_unit ORDER BY accept_time DESC LIMIT 1) AS "guid"
FROM hl_taskhotline AS app where id='230106103710336200' 
GROUP BY app.handle_unit_guid, app.handle_unit