根据 性别 字段 间隔排序 。。。。
一个男 一个女 。。。再一个男一个 女 。而且 根据时间排序
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