使用 Row_Number() 选择行的子集

发布于 2025-01-06 04:42:23 字数 224 浏览 0 评论 0原文

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

我正在尝试选择 50 到 60 之间的行子集。问题是“RowNo”是无效的列名。

感谢您

使用 SQL SERVER 2008 R2

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.

Thank you

Using SQL SERVER 2008 R2

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(2

狂之美人 2025-01-13 04:42:23

使用您的查询作为子查询,如下所示:

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

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

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.

舂唻埖巳落 2025-01-13 04:42:23

您需要执行以下操作:

;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

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

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

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

SQL Server 2012 将具有符合 ANSI SQL 标准的表示法,可以直接基于 ORDER BY 子句进行分页。 请参阅此博文(或其他大量博文)以获取更多信息和更多示例。

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.

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

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.

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文