34,589
社区成员
发帖
与我相关
我的任务
分享
declare @t table(c1 int,c2 int ,c3 int)
insert @t select 11 , 12 , 13
union all select 21 , 22 , 23
union all select 13 , 14 , 15
union all select 22 , 23 , 24
union all select 12 , 13 ,14
union all select 25, 26 ,27
union all select 10,11,12
union all select 23,20,11
union all select 99,88,77
union all select 100,111,112
union all select 112,113,114
select identity(int) id ,c1,c2,c3 into # from @t
select c1,c2,c3,gid=isnull(
(select max(
case when id%2=0
then id-1
else id end
) from #
where (
case when id%2=0
then id-1
else id end
)=(
case when a.id%2=0
then a.id-1
else a.id end
)-4
),
case when id%2=0
then id-1
else id end
) from # a
order by gid,id
/*
这里的gid我为了显示方便列出来的,你要不显示,就把 gid=isnull...去掉,把后面的 order by gid中的 gid替换为 isnull这一串.
c1 c2 c3 c4 gid
1 11 12 13 1
2 21 22 23 1
5 12 13 14 1
6 25 26 27 1
3 13 14 15 3
4 22 23 24 3
7 10 11 12 3
8 23 20 11 3
9 99 88 77 5
10 100 111 112 5
11 112 113 114 7
*/
drop table #
set nocount on
use tempdb
create table T1
(column1 int, column2 int, column3 int)
insert T1
select 11, 12, 13
union all select 21, 22, 23
union all select 13, 14, 15
union all select 22, 23, 24
union all select 12, 13, 14
union all select 25, 26, 27
union all select 10,11,12
union all select 23,20,11
union all select 99,88,77
union all select 100,111,112
union all select 112,113,114
GO
With t_cte AS
(select *, row_number() over(order by current_timestamp) as rowNum from T1)
select column1, column2, column3, rowNum, seqNum
INTO #T1
from
( select *, 2*(row_number() over(order by column1, column2, column3))-1 as seqNum from t_cte where rowNum%2=1
UNION all
select *, NULL as seqNum from t_cte where rowNum%2=0
) a
select column1, column2, column3 from #T1 t1
order by CASE WHEN seqNum is NOT NULL THEN seqNum ELSE (select seqNum+1 from #T1 t2 Where t2.rowNum=t1.rowNum-1) END
GO
drop table #T1
drop table T1