使用Row_Number（）选择行的子集

Select id, name, ROW_NUMBER() OVER (ORDER BY id asc) as 'RowNo'
from customers
where RowNo between 50 AND 60

I am trying to select a subset of rows between 50 and 60 . The problem is 'RowNo' is an invalid column name.

我试图选择50到60之间的行的子集。问题是'RowNo'是一个无效的列名。

Thank you

Using SQL SERVER 2008 R2

使用SQL SERVER 2008 R2

2 个解决方案

#1

Use your query as subquery like bellow:

将您的查询用作子查询,如下:

select * from (
    Select id, name, ROW_NUMBER() OVER (ORDER BY id asc) as [RowNo]
    from customers
) t
where RowNo between 50 AND 60

You can use CTE as well but whether to choose one over another read Difference between CTE and SubQuery? and check execution plan.

您也可以使用CTE,但是是否选择一个而不是另一个读取CTE和SubQuery之间的区别?并检查执行计划。

#2

You need to do something like this:

你需要做这样的事情:

;WITH PaginatingData AS
(
    Select id, name, ROW_NUMBER() OVER (ORDER BY id asc) as 'RowNo'
    from customers
)
SELECT *
FROM PaginatingData
where RowNo between 50 AND 60

Use a CTE (Common Table Expression - sort of an "inline view") as a "wrapper" so that your RowNo becomes a valid column name.

使用CTE(公用表表达式 - 一种“内联视图”)作为“包装器”,以便您的RowNo成为有效的列名。

As an outlook - with SQL Server 2012, you'd be able to write something like this:

作为展望 - 使用SQL Server 2012,您可以编写如下内容:

SELECT 
    id, name
FROM 
    dbo.customers
ORDER BY
    id
OFFSET 50 ROWS
FETCH NEXT 10 ROWS ONLY

SQL Server 2012 will have this ANSI SQL Standard compliant notation to do paging directly based on an ORDER BY clause. See this blog post (or tons of others) for more info and more samples.

SQL Server 2012将使用符合ANSI SQL标准的符号直接根据ORDER BY子句进行分页。有关更多信息和更多示例,请参阅此博客文章(或其他大量文章)。

#1