关于在SQL Server中选择指定的记录范围

问题描述

首先，我想知道是否有一种方法可以选择SQL Server中指定范围的记录，例如从第10条记录到第100条记录.

我认为似乎没有办法(除了第一个位置为0，然后我们使用选择顶部")

但是在C#中，我们有 DataReader ，我想知道这是否是一个很好的解决方法，我想使用 Read()方法前进到记录集中的下一条记录像这样:

First, I wonder if there is a way to select a specified range of records in SQL Server, for example from the 10th record to the 100th record.

I think it seems that there is no way (except that the first position is 0 then we use "select top")

But in C# we have DataReader, I wonder if using this is a good workaround, I want to use the Read() method to advance to the next record in the record set like this:

DataTable SelectRange(int first, int last) {
   DataTable dt = new DataTable();
   OleDbCommand com = new OleDbCommand("Select * from MyTable", con);
   con.Open();
   OleDbDataReader reader = com.ExecuteReader();
   int i = 0;
   //Initialize the schema of dt
   for (; i < reader.FieldCount; i++)
            {
                DataColumn col = new DataColumn(reader.GetName(i));
                dt.Columns.Add(col);
            }
   for(i = 0; i <= first; i++) }
     if(!reader.Read()) return null;
   }
   //Start adding rows to dt
   for(; i <= last; i++) {
      DataRow row = dt.NewRow();
      for(int j = 0; j < reader.FieldCount; j++)
          row[j] = reader[j];
      dt.Rows.Add(row);
      if(!reader.Read()) break;
   }
   reader.Close();
   con.Close();
   return dt;
}

您认为即使在传入的第一个索引达到数百万或更多的情况下，在这里使用 Read()方法也很好吗?
如果没有，请您再给我一个更好的(或也许是最好的)解决此问题的方法(选择范围)！

Do you think using Read() method here is good even when the first index passed in reachs to millions or more???
If not, could you please give me another better (or maybe the best) solution for this problem (selecting a range)!
Your help would be highly appreciated!

推荐答案

WITH myTableWithRows AS (SELECT (ROW_NUMBER() OVER (ORDER BY myTable.SomeField)) as row,* FROM myTable)
SELECT * FROM myTableWithRows WHERE row BETWEEN 10 AND 100

或:

Or:

SELECT * FROM (SELECT *, ROW_NUMBER() OVER (ORDER BY OrderID) AS row FROM myTable ) AS myTableWithRows
WHERE myTableWithRows.row BETWEEN 10 AND 100

select * from (select *,row_number()over(order by cityid ) rowid from CityTable)T where rowid between 10 and 100

这篇关于关于在SQL Server中选择指定的记录范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！