如何检查是否已达到最后一条记录-解网

问：

我有以下代码，它从 tblItems 返回可能的 4 行。但是，如果选择的任何行恰好是 tblItems 中的最后一行，我如何将@lastBox返回为“Y”

我设置了 DECLARE @lastBox varchar（1），我想返回 Y 或 N 来说明它是否是最后一个框。不知道从那里去哪里。

ALTER PROCEDURE [dbo].[SelectBoxes]
@bootSaleDate   DATE,
@itemcategory   Int,
@page  Int

AS
BEGIN
   SET NOCOUNT ON;

   select * from (SELECT ROW_NUMBER() OVER (ORDER BY dateAdded desc) as [RowNo],  bootSaleDate,  '<a href="https://www.myUrl.co.uk/video.aspx?oid='  + CAST(ISNULL([fileID],'') AS NVARCHAR(5)) + '"><br /><image width="140" height="200" src="catalog/thumbnails/' + convertedFilename + '" /></a>' as strText, fileID, [buyPrice] as buyPrice

   FROM [tblItems] WHERE sold='n' AND itemCategory=@itemCategory)t

   where RowNo between (@page*4)-3 AND (@page*4)

   DECLARE @lastBox   varchar(1)

END

sql-服务器

(SELECT ROW_NUMBER() OVER (ORDER BY dateAdded desc)为了警告您，这仍然可能导致非确定性（随机）结果，因为我认为没有主键或唯一键？要始终获得纯确定性（固定）结果，您还应该在具有主键或唯一键的列中包含一个列，假设 id 列具有您应该始终用于获取纯确定性（固定）结果。dateAdded ORDER BYORDER BY dateAdded DESC, id DESC

答：

0赞 Tim Biegeleisen 6/14/2019 #1

将行号与计数进行比较：

SELECT *,
    CASE WHEN RowNo < cnt THEN 'N' ELSE 'Y' END AS box
FROM
(
    SELECT ROW_NUMBER() OVER (ORDER BY dateAdded desc) as [RowNo],
        COUNT(*) OVER () cnt,
        bootSaleDate,  '<a href="https://www.myUrl.co.uk/video.aspx?oid='  + CAST(ISNULL([fileID],'') AS NVARCHAR(5)) + '"><br /><image width="140" height="200" src="catalog/thumbnails/' + convertedFilename + '" /></a>' as strText, fileID, [buyPrice] as buyPrice
    FROM [tblItems]
    WHERE sold = 'n' AND itemCategory = @itemCategory
) t
WHERE RowNo BETWEEN (@page*4)-3 AND (@page*4)

如何检查是否已达到最后一条记录

How to check if last record has been reached

评论

评论