从表中检索最后5行 [英] RETRIEVE THE LAST 5 ROWS FROM A TABLE

问题描述

///如何在不使用临时表的情况下获取此表中的最后5个数据?
Table ="AA"
列名="NAME"
a
B
C
D
E
B
C
D
a
B
C
D
a
B
C
D
E


//输出应为
a
B
C
D
E

//HOW CAN I GET THE LAST 5 DATA IN THIS TABLE WITHOUT USING TEMPORARY TABLES?
Table="AA"
Column name="NAME"
a
B
C
D
E
B
C
D
a
B
C
D
a
B
C
D
E


//Output should be
a
B
C
D
E

推荐答案

如果是SQL 2005或更高版本，则可以使用Row_Number()函数

If it is SQL 2005 or newer you can use Row_Number() function

SELECT x.[Name] 
FROM (
SELECT [Name], Row_Number() OVER (ORDER BY < index fields >) AS RowNumber
FROM AA
) x
WHERE x.RowNumber >= ((SELECT COUNT(*) FROM AA) -5)

Row_Number函数需要有一个排序依据.如果要与磁盘上的顺序相同，请使用聚簇索引字段.

The Row_Number function needs to have an order by. If you want it the same order as what is on the disk use the clustered index field.

对于SQL 2005

For SQL 2005

SELECT  NAME
FROM    (
        SELECT  ROW_NUMBER() OVER( ORDER BY [AlwaysNull]) AS RowNumber,
                NAME
        FROM    (
                SELECT  [AlwaysNull] = NULL,
                        NAME
                FROM    AA
                ) AS a
        ) b
WHERE   RowNumber>(SELECT COUNT(*) FROM AA)-5

//我们也可以通过此方法获取解决方案-


十进制@TAB TABLE(ID INT IDENTITY(1,1)，SNAME VARCHAR(MAX))
从AA插入@TAB(SNAME)选择名称
从@TAB中选择不在其中的ID选择名称
(SELECT TOP(SELECT COUNT(*)-5 FROM @TAB)ID FROM @TAB)

//WE CAN ALSO GET THE SOLUTION BY THIS--


DECLARE @TAB TABLE(ID INT IDENTITY(1,1),SNAME VARCHAR(MAX))
INSERT INTO @TAB(SNAME) SELECT NAME FROM AA
SELECT SNAME FROM @TAB WHERE ID NOT IN
(SELECT TOP(SELECT COUNT(*)-5 FROM @TAB)ID FROM @TAB)

这篇关于从表中检索最后5行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！