select a.id,a.name name1,b.name name2
from (select px=(select count(1) from tb where id<=t.id),* from tb t) a
left join (select px=(select count(1) from tb where id<=t.id),* from tb t) b
on a.px=b.px-1
from (select px=(select count(1) from tb where id<=t.id),* from tb t) a
left join (select px=(select count(1) from tb where id<=t.id),* from tb t) b
on a.px=b.px-1
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([id] int,[name] varchar(4))
insert [tb]
select 1,'张三' union all
select 3,'李四' union all
select 4,'王五' union all
select 5,'马六'
---查询---
select
name as name1,
name2=(select name from (select *,px=(select count(1)+1 from tb where id<t.id) from tb t) a where px=b.px+1)
from
(select *,px=(select count(1)+1 from tb where id<t.id) from tb t) b
---结果---
name1 name2
----- -----
张三 李四
李四 王五
王五 马六
马六 NULL(所影响的行数为 4 行)
条件自己加
go
create table [tb]([id] int,name varchar(10))
insert [tb] select 1,'张三'
union all select 3,'李四'
union all select 4,'王五'
union all select 5,'马六'select a.id,a.name name1,b.name name2
from (select px=(select count(1) from tb where id<=t.id),* from tb t) a
left join (select px=(select count(1) from tb where id<=t.id),* from tb t) b
on a.px=b.px-1
/*
id name1 name2
----------- ---------- ----------
1 张三 李四
3 李四 王五
4 王五 马六
5 马六 NULL(4 行受影响)
*/