select tt1.value,tt1.rn1,tt2.rn2 from (select value,row_number()over(order by value desc) rn1 from t1) tt1, (select value,row_number()over(order by value desc) rn2 from t1 where value<=2) tt2 where tt1.value=tt2.value(+) order by tt1.value desc
作业题还是什么?只要这个格式就行吗? with T AS ( select value,row_number()over(order by value desc) rn from t1 )--前两个不参与第二次排序,两次排序方式一样就不需要在执行一次排序了,数据处理下就行了 SELECT VALUE,RN RN1,(CASE WHEN RN<=2 THEN NULL ELSE RN-2 END) RN2 FROM T order by value desc
(select value,row_number()over(order by value desc) rn1 from t1) tt1,
(select value,row_number()over(order by value desc) rn2 from t1 where value<=2) tt2
where tt1.value=tt2.value(+)
order by tt1.value desc
with T AS (
select value,row_number()over(order by value desc) rn from t1
)--前两个不参与第二次排序,两次排序方式一样就不需要在执行一次排序了,数据处理下就行了
SELECT VALUE,RN RN1,(CASE WHEN RN<=2 THEN NULL ELSE RN-2 END) RN2
FROM T
order by value desc