如何从sql中的特定行开始并跳过上一行并获取所有下一行?
问题描述:
嗨..
我想从特定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: UseRow_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.