如何在 SQL Server 中使用 OFFSET 和 Fetch without Order by

2022-01-03 00:00:00 offset fetch sql-server sql-order-by sql-server-2012

我想在我的 SQL Server 2012 查询中使用 OFFSET 和 Fetch.但没有任何 order by.我不能使用 order by.因为我的排序顺序会丢失.如何在没有 order by 和行号以及查询中的位置的情况下使用 OFFSET 和 Fetch?我的 2 个选择表具有相同的结构.

I want use OFFSET and Fetch in my SQL server 2012 query.But without any order by.I can not use order by.Because my sort order will be lost. How can I use OFFSET and Fetch without order by and row number and where in my query? My 2 select tables have same structure.

INSERT INTO @TempTable [some columns]  
select [some columns] from table1 order by col1 
INSERT INTO @TempTable [same columns]
select [some columns] from table2 order by col2
select * from @TempTable OFFSET 20 ROWS FETCH NEXT 50 ROWS ONLY

此查询在 OFFSET 关键字处有语法错误.

This query has syntax error at OFFSET keyword.

推荐答案

通过向临时表变量添加标识列

By adding an identity column to the temp table variable

    declare @TempTable table([some columns], rownr int identity(1,1) )

    INSERT INTO @TempTable [some columns]  
    select [some columns] from table1  order by col1 

    INSERT INTO @TempTable [same columns]
    select [some columns] from table2 order by col2

为每行添加一个自动递增编号，按照它们添加到临时表的顺序.插入内容不需要填充此列，因此插入内容可以保持原样.然后可以将标识列用于订单:

An automatic incrementing number is added for each row, in the order in which they are added to the temp table. The inserts don't need to fill this column, so the inserts can remain as they are. The identity column can then be used for the order by:

 select * from @TempTable Order by rownr OFFSET 20 ROWS FETCH NEXT 50 ROWS ONLY

相关文章