--try:select tablename.a,tablename.b,tablename.c,rownum+1-tmp.no d from tablename,(select a,min(no) no from (select a,rownum no from tablename) group by a) tmp where tablename.a=tmp.a;
sorry ,缺了一点select col1,col2,col3,row_number() over(partition by col1,col2,col3 order by col1,col2,col3 ) rn from tab1
楼上正解,如果支持分析函数的话。 根据实际情况选择row_number()还是rank()。
看楼主写的结果的例子,是应该用row_number()的。:)
我觉得并不是每种情况都用分析函数好.可以考虑用如下方式,从效率上来说应该是高 Select Rownum,col1,col2,col3 From (Select col1,col2,col3 From table_name Group By col1,col2,col3 Order By col1,col2,col3 )
from tablename,(select a,min(no) no
from (select a,rownum no from tablename)
group by a) tmp
where tablename.a=tmp.a;
根据实际情况选择row_number()还是rank()。
Select Rownum,col1,col2,col3
From (Select col1,col2,col3 From table_name Group By col1,col2,col3 Order By col1,col2,col3 )