使用 group by 从表中选择最后一条记录 [英] Select last records from table using group by

问题描述

我需要使用用户 ID 组从我的表中选择最后一条记录

I need to select last records from my table using group by UserID

我需要从按用户 ID 分组的最后一条记录中选择 StartDate、EndDate

I need to select StartDate,EndDate from last records grouping by userID

我正在使用下面的代码

select StartDate,EndDate from  usersworktime   group by userid,StartDate,EndDate

问题是我得到了所有行，而不是我表的最后两行

The problem is that i am getting all rows instead only the last two of my table

推荐答案

假设开始和结束日期始终是最高值，那么您需要从 GROUP BY(拥有 GROUP BY 中的所有列有点像使用 DISTINCT)并在另一列上使用聚合函数:

Assuming that the start and end dates will always be the highest values then you need to drop some of the columns from the GROUP BY (having all the columns in the GROUP BY is kinda like using DISTINCT) and use an aggregate function on the other column:

SELECT UserId,
       MAX(StartDate) AS StartDate,
       MAX(EndDate) AS EndDate
FROM usersworktime
GROUP BY UserId;

否则，如果不是这种情况，您可以使用 CTE 和 ROW_NUMBER:

Otherwise, if that isn't the case, you can use a CTE and ROW_NUMBER:

WITH CTE AS(
    SELECT UserID,
           StartDate,
           EndDate,
           ROW_NUMBER() OVER (PARTITION BY UserID ORDER BY UsersWordTimeID DESC) AS RN
    FROM usersworktime)
SELECT UserID,
       StartDate,
       EndDate
FROM CTE
WHERE RN = 1;

这篇关于使用 group by 从表中选择最后一条记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！