SQL语句隔双行排序.

今天有人问,就随手写了一段.

效果请粘出来运行.

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 #