用于检索此分组数据的适当 SQL Server 方法？

发布于 2024-09-24 16:56:37 字数 245 浏览 7 评论 0原文

我有一个表 (AU_EMPLOYEE)，其中有两列，分别名为 EmployeeID (int) 和 LastModifiedDate (DateTime)。除了这些列之外，还有其他包含其他员工数据的列。这是一个审计表，每当员工的数据以某种方式发生更改时，就会添加新行。

因此，某个特定员工很可能在此表中拥有多行。我想检索由 LastModifiedDate 确定的每个员工的最新记录。这样做的好方法是什么？嵌套查询或类似的东西？

感谢您的建议。

原文

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

十雾 2024-10-01 16:56:37

您可以使用类似的方法来显示每个员工的最新行。这是 ROW_NUMBER 函数的一个很好的用途。

    with ranking as 
    (
        select *, ROW_NUMBER() over(partition by EmployeeID order by LastModifiedDate desc) as rn
        from AU_EMPLOYEE
    )
    select * from ranking where rn = 1

You could use something like this to show the most recent row for each employee. This is a good use for the ROW_NUMBER function.

    with ranking as 
    (
        select *, ROW_NUMBER() over(partition by EmployeeID order by LastModifiedDate desc) as rn
        from AU_EMPLOYEE
    )
    select * from ranking where rn = 1

回复收藏 0 原文

懷念過去 2024-10-01 16:56:37

SELECT <your columns>
FROM (
SELECT <your columns>,
ROW_NUMBER() OVER(PARTITION BY EmployeeID ORDER BY LastModifiedDate DESC) AS rn
) AS t
WHERE rn=1

SELECT <your columns>
FROM (
SELECT <your columns>,
ROW_NUMBER() OVER(PARTITION BY EmployeeID ORDER BY LastModifiedDate DESC) AS rn
) AS t
WHERE rn=1

回复收藏 0 原文

得不到的就毁灭 2024-10-01 16:56:37

假设至少是 SQL 2005，这样您就可以使用 CTE：

编辑：正如我所指出的这里和这里过去，一定要测试性能。具有 MAX 的 CTE 版本通常会优于基于 ROW_NUMBER 的解决方案。

;with cteMaxDate as (
    select EmployeeID, max(LastModifiedDate) as MaxDate
        from AU_EMPLOYEE
        group by EmployeeID
)
select e.EmployeeID, e.Column1, e.Column2, ...
    from cteMaxDate md
        inner join AU_EMPLOYEE e
            on md.EmployeeID= e.EmployeeID
                and md.MaxDate = e.LastModifiedDate

Assuming at least SQL 2005 so you can use a CTE:

EDIT: As I've pointed out here and here in the past, be sure to test performance. The CTE version with MAX will often outperform a ROW_NUMBER based solution.

;with cteMaxDate as (
    select EmployeeID, max(LastModifiedDate) as MaxDate
        from AU_EMPLOYEE
        group by EmployeeID
)
select e.EmployeeID, e.Column1, e.Column2, ...
    from cteMaxDate md
        inner join AU_EMPLOYEE e
            on md.EmployeeID= e.EmployeeID
                and md.MaxDate = e.LastModifiedDate

回复收藏 0 原文

醉城メ夜风 2024-10-01 16:56:37

克里斯·佩布尔的回答是正确的
然而，更通用的解决方案是

SELECT * FROM
(SELECT EmployeeID, LastModifiedDate
FROM AU_EMPLOYEE
WHERE LastModifiedDate<='X' ORDER BY LastModifiedDate Desc) A
GROUP BY A.EmployeeID

X 是您想要返回的日期。

Chris Pebble's answer is correct
however a more general solution is

SELECT * FROM
(SELECT EmployeeID, LastModifiedDate
FROM AU_EMPLOYEE
WHERE LastModifiedDate<='X' ORDER BY LastModifiedDate Desc) A
GROUP BY A.EmployeeID

where X is the date you want to go back in time to.

回复收藏 0 原文

~没有更多了~