有一个这样数据结构:
ORDERID , SCORE, TIME,C
1 80, 10, 1
2 70, 20, 1
3 40, 30, 1
1 90, 10, 2
2 80, 10, 2
3 0, 10, 2
有一个这样排序结构表,都按照成绩高低partition by排序
我想取出SCORE>=60的第一行,
即:
ORDERID , SCORE, TIME,C
2 70, 20, 1
2 80, 10, 2我写了top来,可是数据很慢,谢谢大家帮助!!!
ORDERID , SCORE, TIME,C
1 80, 10, 1
2 70, 20, 1
3 40, 30, 1
1 90, 10, 2
2 80, 10, 2
3 0, 10, 2
有一个这样排序结构表,都按照成绩高低partition by排序
我想取出SCORE>=60的第一行,
即:
ORDERID , SCORE, TIME,C
2 70, 20, 1
2 80, 10, 2我写了top来,可是数据很慢,谢谢大家帮助!!!
;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语句是这样不知道又不用改进空间!谢谢
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
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
(
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