declare @tab table(ID int primary key,借方金额 int,贷方金额 int)
insert into @tab
select 1,100,0 union all
select 2,200,50 union all
select 3,50,500 union all
select 4,20,100 union all
select 7,30,200
;with sel as (
select *,ROW_NUMBER() over(order by id) as rn,(select SUM(1.0) from @tab) as rnCount from @tab
)
select a.ID,a.借方金额,a.贷方金额,b.ID,b.借方金额,b.贷方金额
from sel a
left join sel b
on a.rn%(ceiling(a.rnCount/2))=b.rn%(ceiling(b.rnCount/2)) and a.rn<b.rn
where a.rn<=ceiling(a.rnCount/2)
