如何从 SQL Server 中选择 y 行之后的数据顶部 x 数据

发布于 2024-10-05 01:18:16 字数 261 浏览 4 评论 0原文

例如，我有一个包含 10'000 行的表。我想选择前 500 行之后的前 100 行。我怎样才能最有效地做到这一点。

SQL Server 2008 所需的查询

例如我已经有这个查询，但我想知道是否有更有效的解决方案

SELECT TOP 100 xx
FROM nn 
WHERE cc NOT IN 
   (SELECT TOP 500 cc
      FROM nn ORDER BY cc ASC)

原文

For example I have a table which contains 10'000 rows. I want to select top 100 rows after top 500th row. How can I do this most efficiently.

Query needed for SQL Server 2008

For example i have this query already but i wonder are there any more effective solution

SELECT TOP 100 xx
FROM nn 
WHERE cc NOT IN 
   (SELECT TOP 500 cc
      FROM nn ORDER BY cc ASC)

分享到QQ

分享到微博

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

发布评论

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

哎呦我呸! 2024-10-12 01:18:16

教程 25：高效分页大量数据

with cte as (
 SELECT ...,
  ROW_NUMBER () OVER (ORDER BY ...) as rn
 FROM ...)
SELECT ... FROM cte 
WHERE rn BETWEEN 500 and 600;

Tutorial 25: Efficiently Paging Through Large Amounts of Data

with cte as (
 SELECT ...,
  ROW_NUMBER () OVER (ORDER BY ...) as rn
 FROM ...)
SELECT ... FROM cte 
WHERE rn BETWEEN 500 and 600;

回复收藏 0 原文

蛮可爱 2024-10-12 01:18:16

Select T0P 600 * 
from my table
where --whatever condition you want
except
select top 500 * 
from mytable
where --whatever condition you want

Select T0P 600 * 
from my table
where --whatever condition you want
except
select top 500 * 
from mytable
where --whatever condition you want

回复收藏 0 原文

清晰传感 2024-10-12 01:18:16

SELECT 
  col1,
  col2
FROM (
    SELECT ROW_NUMBER() OVER (
        ORDER BY [t0].someColumn) as ROW_NUMBER,
    col1,
    col2
    FROM [dbo].[someTable] AS [t0]
    ) AS [t1]
WHERE [t1].[ROW_NUMBER] BETWEEN 501 and 600
ORDER BY [t1].[ROW_NUMBER]

SELECT 
  col1,
  col2
FROM (
    SELECT ROW_NUMBER() OVER (
        ORDER BY [t0].someColumn) as ROW_NUMBER,
    col1,
    col2
    FROM [dbo].[someTable] AS [t0]
    ) AS [t1]
WHERE [t1].[ROW_NUMBER] BETWEEN 501 and 600
ORDER BY [t1].[ROW_NUMBER]

回复收藏 0 原文

栀子花开つ 2024-10-12 01:18:16

选择 TOP 500，然后将 TOP 100 连接到结果集。

通常，为了值得这样做，您需要有一些标准来确定您需要 500 条记录，并且只需要 100 条记录对于另一个条件。我假设这些条件是针对您想要的 TOP 500 的 条件 1，以及针对您想要的 TOP 100 的条件2。由于条件不同，这就是 TOP 100 的记录可能不相同的原因。

select TOP 500 *
    from MyTable
    where -- condition1 -- Retrieving the first 500 rows meeting condition1
union
    select TOP 100 *
        from MyTable
        where -- condition2 -- Retrieving the first 100 rows meeting condition2
-- The complete result set of the two queries will be combined (UNIONed) into only one result set.

EDIT #1

这不是我的意思。我想选择前 500 行之后的前 100 行。因此选择第 501-600 行

在您发表评论后，我更好地理解了您想要实现的目标。试试这个：

WITH Results AS (
    select TOP 600 f.*, ROW_NUMBER() OVER (ORDER BY f.[type]) as RowNumber
        from MyTable f
) select *
    from Results
    where RowNumber between 501 and 600

这有帮助吗？

Selecting TOP 500, then concatenating the TOP 100 to the result set.

Normally, in order to worth doing this, you need to have some criteria on which to base what your need 500 records for, and only 100 for another condition. I assume that these conditions are condition1 for the TOP 500, and condition2 for the TOP 100 you want. Because the conditions differ, that is the reason why the records might not be the same based on TOP 100.

select TOP 500 *
    from MyTable
    where -- condition1 -- Retrieving the first 500 rows meeting condition1
union
    select TOP 100 *
        from MyTable
        where -- condition2 -- Retrieving the first 100 rows meeting condition2
-- The complete result set of the two queries will be combined (UNIONed) into only one result set.

EDIT #1

this is not what i meant. i want to select top 100 rows coming after top 500 th row. so selecting rows 501-600

After your comment, I better understood what you want to achieve. Try this:

WITH Results AS (
    select TOP 600 f.*, ROW_NUMBER() OVER (ORDER BY f.[type]) as RowNumber
        from MyTable f
) select *
    from Results
    where RowNumber between 501 and 600

Does this help?

回复收藏 0 原文

~没有更多了~