在工作中遇到一个问题：
一个活动有两个游戏关卡：第一关、第二关。
用户进入某一关且在要求的时间内闯关成功，就会在数据库中记录用户闯过这一关的时间，否则这一关的时间默认为0；
数据库中保存的数据格式如下：



目前项目需求是：
如果用户通过了第一关和第二关，就显示在最上面；
如果用户只通过了第一关，就显示在中间；
如果用户两关都没有参加或通过，就显示在最后；
以上是显示的先后顺序，且这些数据都要按照时间相加后从小到大排列。
求高手帮写个sql语句实现效果
格式如下：
排序号   姓名        第一关时间        第二关时间
1         a               10.32            23.37
2         b               8.43             15.33
3         c               11.35            28.47
4         d               11.00            31.27
5         e               19.55            0
6         f               21.37            0
7         g               21.42            0
8         h               30.33            0
9         i               33.00            0
10        j               0                0
11        k               0                0
12        l               0                0
13        m               0                0

数据库附件如下：

[此贴子已经被作者于2016-5-27 11:09编辑过]

--测试环境 SQL2000,建立测试数据,#games 为SQL2000的临时表名称
create table #games(NickName varchar(12),One_Best_Time numeric(5,2),Two_Best_Time numeric(5,2))
insert into #games(NickName,One_Best_Time,Two_Best_Time) values('张1',20.12,24.21)
insert into #games(NickName,One_Best_Time,Two_Best_Time) values('张2',10.22,20.12)
insert into #games(NickName,One_Best_Time,Two_Best_Time) values('张3',21.22,20.12)
insert into #games(NickName,One_Best_Time,Two_Best_Time) values('张4',13.22,22.12)
insert into #games(NickName,One_Best_Time,Two_Best_Time) values('张5',14.21,42.12)
insert into #games(NickName,One_Best_Time,Two_Best_Time) values('张6',15.22,12.12)
insert into #games(NickName,One_Best_Time,Two_Best_Time) values('张7',16.24,22.12)
insert into #games(NickName,One_Best_Time,Two_Best_Time) values('张8',17.52,24.12)
insert into #games(NickName,One_Best_Time,Two_Best_Time) values('张9',18.62,12.12)

insert into #games(NickName,One_Best_Time,Two_Best_Time) values('李1',21.22,0.0)
insert into #games(NickName,One_Best_Time,Two_Best_Time) values('李2',11.32,0.0)
insert into #games(NickName,One_Best_Time,Two_Best_Time) values('李3',21.42,0.0)
insert into #games(NickName,One_Best_Time,Two_Best_Time) values('李4',41.65,0.0)

insert into #games(NickName,One_Best_Time,Two_Best_Time) values('王1',0.0,0.0)
insert into #games(NickName,One_Best_Time,Two_Best_Time) values('王2',0.0,0.0)
insert into #games(NickName,One_Best_Time,Two_Best_Time) values('王3',0.0,0.0)
insert into #games(NickName,One_Best_Time,Two_Best_Time) values('王4',0.0,0.0)

--借1个字段来完成排续，没完成的默认完成最大时间为 999.99
select *,(one_best_time + two_best_time)id from #games where one_best_time>0 and two_best_time>0
union all
select *,(one_best_time + 999.99)id from #games where one_best_time>0 and two_best_time<=0
union all
select *,(999.99 + 999.99)id from #games where one_best_time<=0 and two_best_time<=0
order by id

2楼的缺点是：有个默认最大时间 999.99，下面的缺是 TOP 99999,超过99999条记录后，每种的就不显示后面的。
select * from (select top 99999 (one_best_time + two_best_time) id,* from #games where one_best_time>0 and two_best_time>0 order by id asc)g1
union all
select * from (select top 99999 (one_best_time + two_best_time) id,* from #games where one_best_time>0 and two_best_time=0 order by id asc)g2
union all
select * from (select top 99999 (one_best_time + two_best_time) id,* from #games where one_best_time=0 and two_best_time=0 order by id asc)g3

图片附件: 游客没有浏览图片的权限，请 登录 或 注册

[此贴子已经被作者于2016-5-28 11:43编辑过]

谢谢大牛，第一种方式突破了top的那种局限，是很好用的，第二种方式代码会稍微简单一点，我都测试了一下，能得到想要的效果，又get了一种新技能呀，哈哈，谢谢帮忙。