create table tb(id varchar(10),姓名 nvarchar(10),年龄 int)
insert into tb select '01','李白',20
insert into tb select '02','杜甫',21
insert into tb select '03','张飞',22
insert into tb select '04','宋江',23  --假如这里改成23
insert into tb select '05','岳飞',25
go
;with cte as(
select 1 as rn,id as id1,* from tb a where exists(select 1 from tb where 年龄=a.年龄+1)
and not exists(select 1 from tb where 年龄=a.年龄-1)
union all
select a.rn+1,a.id1,b.* from cte a inner join tb b on a.年龄=b.年龄-1
)select a.id,a.姓名,a.年龄,b.id as id2,b.姓名 as 姓名2,b.年龄 as 年龄2 
from cte a inner join cte b on a.id1=b.id1 and a.rn=b.rn-1
where exists(select 1 from cte where id1=a.id1 and rn=(select max(rn) from cte))
/*
id         姓名         年龄          id2        姓名2        年龄2
---------- ---------- ----------- ---------- ---------- -----------
01         李白         20          02         杜甫         21
02         杜甫         21          03         张飞         22
03         张飞         22          04         宋江         23

(3 行受影响)

*/
go
drop table tb

------解决方案--------------------
探讨

如果改宋江的年龄:

SQL code
create table tb(id varchar(10),姓名 nvarchar(10),年龄 int)
insert into tb select '01','李白',20
insert into tb select '02','杜甫',21
insert into tb select '03','张飞',22
insert into t……


------解决方案--------------------
看着这些高手我们这些菜鸟还怎么混哪