Create TABLE Pet
(
[Id] int identity(1,1) primary key,
[Name] nvarchar(50)
)方法一：SQL Server2000经典查询法(TOP)
Select TOP 1 [Name] FROM Pet Where [Id]=(Select max([Id]) FROM Pet Where [Id] in (Select TOP 2 [Id] FROM Pet orDER BY [Id]))简化版：
Select TOP 1 [Name] FROM Pet Where [Id]=(Select max([Id]) FROM (Select TOP 2 [Id] FROM Pet orDER BY [Id]) as tab)若N需动态指定，可用sp_executesql：
declare @sql nvarchar(200)
declare @Name nvarchar(50), @Number int
select @Number = 2, @sql = N'Select TOP 1 @Name=[Name] FROM Pet Where [Id]=(Select max([Id]) FROM (Select TOP ' + convert(nvarchar,@Number) + ' [Id] FROM Pet orDER BY [Id]) as tab)'
EXECUTE sp_executesql @sql, N'@Name nvarchar(50) output', @Name output
Select @NameSQL Server2005增强型TOP(N)法：
Select TOP 1 [Name] FROM Pet Where [Id]=(Select max([Id]) FROM (Select TOP(@Number) [Id] FROM Pet orDER BY [Id]) as tab)方法二：SQL Server2005新查询法(row_number() over())
Select [Name] FROM (Select (row_number() over(ORDER BY [Id])) as RowNumber,[Name] FROM Pet) as tab Where RowNumber=2
评论（0）
暂无评论。