34,576
社区成员
发帖
与我相关
我的任务
分享
declare @t table(f1 int identity,f2 varchar(100))
insert @t select 'Name1'
insert @t select 'Name3'
insert @t select 'Name2'
insert @t select 'Name6'
insert @t select 'Name4'
insert @t select 'Name5'
insert @t select 'Name10'
insert @t select 'Name11'
insert @t select 'Name8'
insert @t select 'Name7'
insert @t select 'Name9'
;WITH temp
AS
(
SELECT ROW_NUMBER() OVER (ORDER BY f2)AS [序号],f2 AS [物品] FROM @t
)
SELECT * FROM
(
SELECT * FROM temp WHERE [序号] <= ((SELECT MAX([序号]) FROM temp) + 1 )/2
)A
LEFT JOIN
(
SELECT * FROM temp WHERE [序号] > ((SELECT MAX([序号]) FROM temp) + 1 )/2
)B ON A.[序号] + ((SELECT MAX([序号]) FROM temp) + 1 )/2 = B.[序号]
GO
select 物品,序号=identity(int,1,1) into # from [Table] order by 物品
select * from
(select top 50 * from # order by 序号)aa
left join
(select * from # where 序号>50)bb
on aa.序号=bb.序号-50
僅供參考
declare @t table(ID int identity,[Name] varchar(100))
insert @t select 'Name1'
insert @t select 'Name2'
insert @t select 'Name3'
insert @t select 'Name4'
insert @t select 'Name5'
insert @t select 'Name6'
insert @t select 'Name7'
insert @t select 'Name8'
insert @t select 'Name9'
insert @t select 'Name10'
insert @t select 'Name11'
select
max(case when ID%2=1 then [Name] else null end),
max(case when ID%2=0 then [Name] else null end)
from @t
group by (ID+1)/2
/*
---------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------
Name1 Name2
Name3 Name4
Name5 Name6
Name7 Name8
Name9 Name10
Name11 NULL
警告: 彙總或其他 SET 作業已刪除 Null 值。
(6 個資料列受到影響)
*/