行号取数

Question

我的表包含大约5000行。我想知道表中特定的行位置。我的存储过程是：

 CREATE PROC Sp_getrownumber(@MemberID INT) 
AS 
    SELECT Row_number() 
             OVER (  ORDER BY joiningdate ASC) AS Rownumber, 
           M.memberid, 
           P.name, 
           M.joiningdate 
    FROM   tblmemberdata M 
           JOIN tblpersonaldetails P 
             ON M.memberid = P.memberid 

但是，当我尝试2获取特定行时，行no显示为1。代码为：

 CREATE PROC Sp_getrownumber(@MemberID INT) 
AS 
    SELECT Row_number() 
             OVER (  ORDER BY joiningdate ASC) AS Rownumber, 
           M.memberid, 
           P.name, 
           M.joiningdate 
    FROM   tblmemberdata M 
           JOIN tblpersonaldetails P 
             ON M.memberid = P.memberid 
    WHERE  M.memberid = @MemberID

如何获取准确的行号？

Answer 1

将其修改为：

alter proc SP_GetRowNumber
(@MemberID int)
as
SELECT * FROM (
  SELECT
ROW_NUMBER() OVER (ORDER BY JoiningDate ASC) AS Rownumber,
M.MemberID,P.Name,M.JoiningDate
FROM tblMemberData M join tblPersonalDetails P on M.MemberID=P.MemberID
) AS Counts
WHERE MemberID = @MemberID

Answer 2

您看到的是因为ROW_NUMBER()只在本地SELECT 上工作，而不是在整个表上工作！

试试这个：

CREATE PROC Sp_getrownumber(@MemberID INT) 
AS
    SELECT *
    FROM (
        SELECT Row_number() 
                 OVER (ORDER BY joiningdate ASC) AS Rownumber, 
               M.memberid, 
               P.name, 
               M.joiningdate 
        FROM   tblmemberdata M 
        JOIN tblpersonaldetails P 
               ON M.memberid = P.memberid
    ) tblRowCounter
    WHERE M.memberid = @MemberID;

这段代码完成的是对保留原始行号的内存表执行过滤。

注意，中没有行号。SQL只知道有一大堆数据属于一个表。OVER子句中的ORDER BY joiningdate ASC语句是非常重要的！更改该列或添加更多列可能会更改行号。

Answer 3

CREATE PROC Sp_getrownumber(@MemberID INT) 
AS 
    ;WITH Numbered AS (
      SELECT Row_number() 
               OVER (  ORDER BY joiningdate ASC) AS Rownumber, 
             M.memberid, 
             P.name, 
             M.joiningdate 
       FROM   tblmemberdata M 
           JOIN tblpersonaldetails P 
             ON M.memberid = P.memberid 
    )
    SELECT *
    FROM Numbered
    WHERE memberid = @MemberID