如何在SQL Server中使用OFFSET和无顺序获取 [英] How to use OFFSET and Fetch without Order by in SQL Server

问题描述

我想在SQL Server 2012查询中使用OFFSET和Fetch.但是没有任何by 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

这篇关于如何在SQL Server中使用OFFSET和无顺序获取的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！