如何从sql中的特定行开始并跳过上一行并获取所有下一行？ [英] How to start from particular row in sql and skip previous and take all next rows ?

问题描述

嗨..



我想从特定id获取sql行的元素，然后从那一点开始，并希望跳过之前的记录。



请建议。



我的尝试：

Hi ..

I want to take take elements of sql rows from particular id and next from that point and want to skip previous records of that.

Please suggest .

What I have tried:

Declare @TopLocationCode NVARCHAR(15) ='Company'
Declare @LeastLoactionCode NVARCHAR(15) ='Area'
Declare @SearchLocationText NVARCHAR(100) = NULL
Declare @SearchLocationType INT = NULL
DECLARE @Rank INT

	SELECT	@Rank = Rank, 
			@SearchLocationType = CASE WHEN @SearchLocationType IS NULL THEN lt.LocationTypeID ELSE @SearchLocationType END
	FROM LocationType lt 
	WHERE LTRIM(RTRIM(lt.Code)) = LTRIM(RTRIM(@LeastLoactionCode));

       WITH HierarchyP
		   AS ( 
				SELECT l.LocationID,l.ParentLocationID
				FROM Location l
				INNER JOIN LocationType lt ON l.LocationTypeID = lt.LocationTypeID
				WHERE l.IsInActive = 0 AND LTRIM(RTRIM(lt.Code)) = LTRIM(RTRIM(@TopLocationCode))  

				UNION ALL

				SELECT l.LocationID, l.ParentLocationID
				FROM Location l
				INNER JOIN LocationType lt ON l.LocationTypeID = lt.LocationTypeID
				INNER JOIN HierarchyP ON				 
				l.ParentLocationID = HierarchyP.LocationID
				WHERE l.IsInActive = 0 AND Rank <= @Rank
		)

        SELECT DISTINCT
			l.LocationID,
			l.Code,
			l.Name,
			l.ParentLocationID ,
			lt.Code AS LocationTypeCode,
			lt.Rank  
		FROM 
		Location l INNER JOIN HierarchyP h ON l.LocationID = h.LocationID
		INNER JOIN LocationType lt ON lt.LocationTypeID = l.LocationTypeID
		WHERE ((ISNULL(@SearchLocationText,'') = '') 
		OR (l.LocationTypeID <> @SearchLocationType) 
		OR (l.LocationTypeID = @SearchLocationType AND l.Name LIKE '%' + @SearchLocationText + '%'))

推荐答案

您可以创建一个变量并存储lastRd的lastId，然后在查询中放置where条件以获取该ID之后的记录。



喜欢：

@ lastId = 10;



从yourtable中选择* yourTableId> @lastId





Ashish Nigam

Hi,

You can make a variable and store lastId of lastRow, then put a where condition on your query to get records after that Id

like:
@lastId=10;

select * from yourtable where yourTableId>@lastId


Ashish Nigam

尝试使用以下选项。以下查询从6到10获取5条记录：



Option1 ：使用 Row_Number

Try with below options. Following query fetches 5 records from 6 to 10:

Option1: Use Row_Number

SELECT  *
FROM    (	  
		  SELECT DISTINCT ROW_NUMBER() OVER (ORDER BY LocationID) AS RowNum,			
			l.LocationID,
			l.Code,
			l.Name,
			l.ParentLocationID ,
			lt.Code AS LocationTypeCode,
			lt.Rank  
		FROM 
			Location l INNER JOIN HierarchyP h ON l.LocationID = h.LocationID
			INNER JOIN LocationType lt ON lt.LocationTypeID = l.LocationTypeID
			WHERE ((ISNULL(@SearchLocationText,'') = '') 
			OR (l.LocationTypeID <> @SearchLocationType) 
			OR (l.LocationTypeID = @SearchLocationType AND l.Name LIKE '%' + @SearchLocationText + '%'))			
        ) AS TempResult
WHERE   RowNum > 5
    AND RowNum <= 10
ORDER BY RowNum

Option2 ：使用抵消和获取

Option2: Use Offset and Fetch

SELECT 		l.LocationID,
			l.Code,
			l.Name,
			l.ParentLocationID ,
			lt.Code AS LocationTypeCode,
			lt.Rank  
		FROM 
			Location l INNER JOIN HierarchyP h ON l.LocationID = h.LocationID
			INNER JOIN LocationType lt ON lt.LocationTypeID = l.LocationTypeID
			WHERE ((ISNULL(@SearchLocationText,'') = '') 
			OR (l.LocationTypeID <> @SearchLocationType) 
			OR (l.LocationTypeID = @SearchLocationType AND l.Name LIKE '%' + @SearchLocationText + '%'))
ORDER BY l.LocationID ASC
OFFSET 5 ROWS
FETCH NEXT 5 ROWS ONLY

注意：抵消-Fetch仅适用于病房的SQLSrever 2012.

Note: Offset-Fetch only works SQLSrever 2012 on wards.

这篇关于如何从sql中的特定行开始并跳过上一行并获取所有下一行？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！