create table t (id int, name varchar(2)) --drop table tinsert into t select 1, 'n1' union select 2, 'n2' union select 3, 'n3' union select 4, 'n4'select a.id, a.name, b.id, b.name from ( select (select count(*) from t a where t.id>a.id) cnt,* from t where (select count(*) from t a where t.id>a.id) < (select count(*) from t)/2 ) a left join ( select ((select count(*) from t a where t.id>a.id)-(select count(*) from t)/2) cnt,* from t where (select count(*) from t a where t.id>a.id) >= (select count(*) from t)/2 ) b on a.cnt=b.cnt where a.cnt < (select count(*) from t)/2--------结果---------- id name id name ----------- ---- ----------- ---- 1 n1 3 n3 2 n2 4 n4(所影响的行数为 2 行)
select * from (select sid=id/cnt,ssid=id mod cnt,id,ch from table) a join (select sid=id/cnt,ssid=id mod cnt,id,ch from table) b on (b.sid>a.sid and b.ssid=a.ssid) 其中cnt为表中id值的一半
create table t (id int, name varchar(2))insert into t select 1, 'n1' union select 2, 'n2' union select 3, 'n3' union select 4, 'n4' GO Select Max(Case (id - 1) / 2 When 0 Then id Else Null End) As 序号1, Max(Case (id - 1) / 2 When 0 Then name Else Null End) As 车号1, Max(Case (id - 1) / 2 When 1 Then id Else Null End) As 序号2, Max(Case (id - 1) / 2 When 1 Then name Else Null End) As 车号 From t Group By id % 2 Order By 序号1 GO Drop Table t /* 序号1 车号1 序号2 车号2 1 n1 3 n3 2 n2 4 n4 */
--drop table tinsert into t
select 1, 'n1' union
select 2, 'n2' union
select 3, 'n3' union
select 4, 'n4'select a.id, a.name, b.id, b.name
from (
select (select count(*) from t a where t.id>a.id) cnt,*
from t
where (select count(*) from t a where t.id>a.id) < (select count(*) from t)/2
) a
left join (
select ((select count(*) from t a where t.id>a.id)-(select count(*) from t)/2) cnt,*
from t
where (select count(*) from t a where t.id>a.id) >= (select count(*) from t)/2
) b
on a.cnt=b.cnt
where a.cnt < (select count(*) from t)/2--------结果----------
id name id name
----------- ---- ----------- ----
1 n1 3 n3
2 n2 4 n4(所影响的行数为 2 行)
(select sid=id/cnt,ssid=id mod cnt,id,ch from table) b on (b.sid>a.sid and b.ssid=a.ssid)
其中cnt为表中id值的一半
select 1, 'n1' union
select 2, 'n2' union
select 3, 'n3' union
select 4, 'n4'
GO
Select
Max(Case (id - 1) / 2 When 0 Then id Else Null End) As 序号1,
Max(Case (id - 1) / 2 When 0 Then name Else Null End) As 车号1,
Max(Case (id - 1) / 2 When 1 Then id Else Null End) As 序号2,
Max(Case (id - 1) / 2 When 1 Then name Else Null End) As 车号
From
t
Group By
id % 2
Order By
序号1
GO
Drop Table t
/*
序号1 车号1 序号2 车号2
1 n1 3 n3
2 n2 4 n4
*/