选择SQL Server中每位员工的前5条记录 [英] Select Top 5 records of every employee in SQL Server

问题描述

我遇到以下问题，我有一个查询，该查询选择了为雇员创建的最新5条记录:

I have the following issue, I have this query that select the latest 5 records created for an employee:

SELECT TOP 5
       p.value,
       p.record_date AS FECHA
FROM 
    employee_loan_movements p
WHERE
     p.employee_code = '1'
     AND p.record_date <= '2009-11-11'
     AND p.movement_type = 1
     AND p.value > 0
ORDER BY p.record_date DESC

现在我需要建立一个查询来选择loan_movements表中每位员工的前5名，我知道我可以通过选择选择rownum和rownum< = 5来在Oracle中做到这一点，但我无法设法找到一种方法在SQL Server 2000中执行该操作或执行其他操作可获得相同的结果.

Now i need to build a query to select the top 5 of every employee in the loan_movements table, I know i can do it in Oracle by selecting selecting rownum and rownum <= 5 but I cant manage to find a way to do that or something else with the same result in SQL Server 2000.

我想我可以使用一个函数，但是我想首先知道它是否可以在普通SQL中完成.

I guess I could use a function but I want to know first if it can be done in plain SQL.

感谢您的帮助...

推荐答案

在注意到您使用的是SQL Server 2000之前，我已回答，因此您无法利用 MS知识库文章提供了示例如何人为地对行进行排名.

I answered before noticing you're on SQL Server 2000, so you couldn't take advantage of ROW_NUMBER. However, this MS Knowledgebase article gives examples how to artificially rank rows.

排名生效后，请在嵌入式视图中使用查询:

Once you have the ranking working, use the query in an inline view:

SELECT x.*
  FROM (SELECT p.value,
               ... 'rank'
          FROM EMPLOYEE_LOAN_MOVEMENTS p) x
 WHERE x.rank <= 5

这篇关于选择SQL Server中每位员工的前5条记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！