更新时间:2023-02-05 19:03:36
您可以将 WITH TIES
选项与窗口函数 lead()
和 一起使用>row_number()
You can use the WITH TIES
option in concert with the window functions lead()
and row_number()
示例
Declare @YourTable Table ([Id] varchar(50),[Order] int,[Value] varchar(50)) Insert Into @YourTable Values
('aa',0,'cat')
,('ba',1,'dog')
,('bb',2,'yuk')
,('dc',3,'gen')
,('ca',4,'cow')
,('c1',5,'owl')
,('b0',7,'ant')
,('h9',8,'fly')
,('t4',9,'bee')
,('g2',10,'fox')
,('ea',11,'rat')
,('fa',12,'pig')
,('gu',13,'pig')
,('co',14,'pig')
,('fo',15,'pig')
,('ou',16,'pig')
,('eo',17,'pig')
,('ii',18,'pig')
Select top 1 with ties *
From @YourTable
Order By case when lead(value,1) over (order by [order]) = value then 1 else 2 end
,row_number() over (order by [Order])
结果
Id Order Value
fa 12 pig