sql:对字段排序并取其序号

[alexwoowf]

有一表：field1
200201011
200201091
200201033
200201045
200201045 如何让他新增itemno列
1
5
2
3
3
（在http://expert.csdn.net/Expert/topic/1275/1275333.xml?temp=.0584833中有这个问题，不过最终crazyfor（现在不知改成什么了*^_^*）的答案是从0开始的
select field1,(select count(*) from table where right(field1,5)<right(A.field1,5)) from table A
我将〈改成〈= 后从1开始，但是itemno是1 5 2 4 4！ help.........

[hjhing]

select field1,(select count(*) from table where right(field1,5)<right(A.field1,5))+1 as rowsid from table A

[hjhing]

select identity(int,1,1) as sno, field1
into #tt
from tableA
order by field1

select * from #tt

[愉快的登山者]

select (select count(itemno) from tablename where itemno < A.itemno ) as rownum,
* from tablename as A order by itemno

[Chiff]

呵呵呵...

[alexwoowf]

请注意看题，应该说得很清楚了吧？

[alexwoowf]

这个问题很好笑吗？chiff（一对翅膀）~！·#￥%……—

[pengdali]

看不懂题目！
是：
select field1,(select count(*)+1 from table where right(field1,5)<right(A.field1,5)) from table A
吗？？

[lizongqi]

up