求一个“简单”的查询

[icanflying]

一张表只有一列，列中有若干行，可以通过select count(*) from 表 查出行数。
要求是：查询结果为将该表平均分成两列，每列为一半数据。
如：
列一
_____
1
2
3
4
查询结果为：
列一 列二
___________________________
1 3
2 4

[lsxaa]

select a.col as 列一,b.col as 列二
from t a inner join t b
on (select count(*) from t where id<a.id)
=(select count(*) from t where id<b.id)+1

[comszsoft]

关注

[yesyesyes]

楼主是不是想分列打印?
上次有个朋友把这个问题弄得很复杂,
其实只要在报表中去做,很简单的.

[syilo]

alter table 表 add TID int default identity(1,1)
declare @ID int
select @ID = max(ID) from 表
select *,列一 as 列二 from 表 where ID > (@ID/2 - 1)
delete from 表 where ID > (@ID/2 -1)

[tengjian1981]

select NewID=IDENTITY(int,1,1),ID,Name into #TempA from TableName
select * from
(
select NewID,ID,Name from #TempA
where ID<=(select (Count(*)+1)/2 from #TempA)
) a
left join
(
select NewID-(select (Count(*)+1)/2 from #TempA) as New ID,ID,Name from #TempA
where ID>(select (Count(*)+1)/2 from #TempA)
) b on a.NewID=b.NewID

[lsxaa]

select a.col as 列一,b.col as 列二
from t a left join t b
on (select count(*) from t where id<a.id)
=(select count(*) from t where id<b.id)+1

[peachlee]

楼上小李大师的有些不对，少了东西，正确的是
select b.id as 列二,a.id as 列一
from t a inner join t b
on (select count(*) from t where id<a.id)-1
=(select count(*) from t where id<b.id)+1

[tengjian1981]

上面那个写的有点粗心,这个我自己已经试过了是可行的

select NewID=IDENTITY(int,1,1),ID,Name into #TempA from TableName
select a.ID,a.Name,b.ID,b.Name from
(
select NewID,ID,Name from #TempA
where NewID<=(select (Count(*)+1)/2 from #TempA)
) a
left join
(
select NewID-(select (Count(*)+1)/2 from #TempA) as NewID,ID,Name from #TempA
where NewID>(select (Count(*)+1)/2 from #TempA)
) b on a.NewID=b.NewID
Drop Table #TempA

[lsxaa]

成大师了???

select a.col as 列一,b.col as 列二
from t a left join t b
on (select count(*) from t where id<=a.id) --这里应<=
=(select count(*) from t where id<=b.id)+1 --这里应<=