从数据库表中检索第n个选项 [英] Retrieve nth option from a database table

问题描述

我正在使用C＃和ADO.Net实体数据模型开发应用程序。我有一个表用户在SQL Server数据库中有3列（idUser，nameUser，statusUser）。如何检索具有statusUser == 0的表的第二个或第三个或第N个用户？

解决方案

您需要定义存储过程 [ ^ ]。

  CREATE   PROCEDURE  GetNthUserByStatus 
  @ stat   INT ，
  @ nth   INT  
  AS  
  BEGIN  
  SELECT  idUser，nameUser，statusUser 
  FROM （
  SELECT  idUser，nameUser，statusUser，ROW_NUMBER （） OVER （ ORDER   BY  idUser）  AS  RowNo 
  FROM  [用户] 
  WHERE  statusUser =  @ stat  
） AS  T 
  WHERE  T.RowNo =  @ nth  
  END  

之后，您需要从代码中调用此存储过程。怎么样？请参阅：如何：使用ADO.NET和Visual C＃.NET调用参数化存储过程 [ ^ ]

如果您想要第2条记录，这个怎么样....

  SELECT   TOP   1  * 
  FROM  myTable 
  WHERE  ID  NOT   IN （ SELECT   TOP   1  ID  FROM  myTable  ORDER   BY  ID ）
  ORDER   BY  ID 

I''m developping an application with C# and ADO.Net entity data model. I have a table Users in SQL Server database with 3 columns (idUser, nameUser, statusUser). How can i retrieve the second or the third or the Nth user of the table that have statusUser == 0?

解决方案

You need to define stored procedure[^].

CREATE PROCEDURE GetNthUserByStatus
    @stat INT,
    @nth INT
AS
BEGIN
SELECT idUser, nameUser, statusUser
FROM (
    SELECT idUser, nameUser, statusUser, ROW_NUMBER() OVER(ORDER BY idUser) AS RowNo
    FROM [Users]
    WHERE statusUser = @stat
    ) AS T
WHERE T.RowNo = @nth
END

After that, you need to call this stored procedure from code. How? See this: HOW TO: Call a Parameterized Stored Procedure by Using ADO.NET and Visual C# .NET[^]

If you want the 2nd record, how about this....

SELECT TOP 1 *
FROM myTable
WHERE ID NOT IN ( SELECT TOP 1 ID FROM myTable ORDER BY ID )
ORDER BY ID

这篇关于从数据库表中检索第n个选项的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！