根据 性别 字段 间隔排序 。。。。

根据 性别 字段 间隔排序 。。。。

一个男 一个女 。。。再一个男一个 女 。而且 根据时间排序

 

 

create table tb(bsex varchar(2),dt datetime)
insert into tb values('男','2010-11-01')
insert into tb values('男','2010-11-02')
insert into tb values('女','2010-11-03')
insert into tb values('男','2010-11-04')
insert into tb values('男','2010-11-05')
insert into tb values('女','2010-11-06')
insert into tb values('女','2010-11-07')
go

with t1 as
(
    select rowid = (row_number() over(order by dt))*2-1,* from tb where bsex = '男'
)
,t2 as
(
    select rowid = (row_number() over(order by dt))*2,* from tb where bsex = '女'
)
select * from t1 union all select * from t2 order by rowid

rowid                bsex dt
-------------------- ---- -----------------------
1                    男    2010-11-01 00:00:00.000
2                    女    2010-11-03 00:00:00.000
3                    男    2010-11-02 00:00:00.000
4                    女    2010-11-06 00:00:00.000
5                    男    2010-11-04 00:00:00.000
6                    女    2010-11-07 00:00:00.000
7                    男    2010-11-05 00:00:00.000

(7 行受影响)

或者: with tmp
as(
select rowid=((row_number() over(order by dt))*2 - 1),* from tb where bsex='男'
union all
select rowid=((row_number() over(order by dt))*2 ),* from tb where bsex='女')
select * from tmp order by rowid

 

 

原贴地址：http://topic.csdn.net/u/20101122/11/3a386535-47ce-4e22-8afe-c019222ede64.html?30713