34,575
社区成员
发帖
与我相关
我的任务
分享
;with tab(ORDERID,SCORE,TIME,C) as
(
select rank() over(partition by C order by SCORE desc) as ORDERID,
SCORE, TIME,C from # with(nolock)
)select ORDERID , TIME,C,
(select top 1 SCORE from tab where SCORE>=60 and C=a.C order by SCORE asc) SCORE
from tab a where ORDERID=2
create table #(SCORE int,[TIME] int ,C int)
insert into #
select 80,10,1 union all
select 70,20,1 union all
select 40,30,1 union all
select 90,10,2 union all
select 80,10,2 union all
select 0,10,2
CREATE INDEX IX_Order ON # (c ASC,SCORE DESC )
CREATE INDEX IX_Order2 ON # (SCORE asc )
;with tab as
(
select rank() over(partition by C order by SCORE desc) as ORDERID,
SCORE, TIME,C from # with(nolock)
)select ORDERID , TIME,C,
(select top 1 SCORE from tab where SCORE>=60 order by SCORE asc) SCORE
from tab where ORDERID=2
create table #(SCORE int,[TIME] int ,C int)
insert into #
select 80,10,1 union all
select 70,20,1 union all
select 40,30,1 union all
select 90,10,2 union all
select 80,10,2 union all
select 0,10,2
;with tab as
(
select rank() over(partition by C order by SCORE desc) as ORDERID,
SCORE, TIME,C from # with(nolock)
)select ORDERID , TIME,C,
(select top 1 SCORE from tab where SCORE>=60 order by SCORE asc) SCORE
from tab where ORDERID=2
;with tab as
(
select rank() over(partition by C order by SCORE desc) as ORDERID,
SCORE, TIME,C from ActPass with(nolock)
)select ORDERID , TIME,C
(select top 1 SCORE from tab where SCORE>=60 order by SCORE asc ) SCORE,
from tab where ORDERID=1
语句是这样不知道又不用改进空间!谢谢