一个sql的问题，怎么在查询中增加一个自增列（给出结果立刻结帖）
比如查询语句select stor_id,stor_name,stor_address from pubs..stores的结果中为
stor_id stor_name stor_address
6380Eric the Read Books788 Catamaugus Ave.
7066Barnum 's567 Pasadena Ave.
7067News & Brews577 First St.
7131Doc-U-Mat: Quality Laundry and Books24-A Avogadro Way
7896Fricative Bookshop89 Madison St.
8042Bookbeat679 Carson St.
我想在结果中增加一列，形式为：
id stor_id stor_name stor_address
1 6380Eric the Read Books788 Catamaugus Ave.
2 7066Barnum 's567 Pasadena Ave.
3 7067News & Brews577 First St.
4 7131Doc-U-Mat: Quality Laundry and Books24-A Avogadro Way
5 7896Fricative Bookshop89 Madison St.
6 8042Bookbeat679 Carson St.
请大家帮下忙，环境是mssql2000
[解决办法]
select identity(1,1),stor_id,stor_name,stor_address from pubs.stores
[解决办法]
select id=(select count(*)+1 from stores where stor_id <a.stor_id),* from stores a
[解决办法]
多加个条件不能解决?
--try
select id=(select count(*)+1 from stores where col=a.col and stor_id <a.stor_id),* from stores a
[解决办法]
可以用rank()函数吗？
就是
select stor_id,stor_name,stor_address，rank()over(partion by stor_id)as rank
[解决办法]
select stor_id,stor_name,stor_address，rank()over(partion by stor_id)as rank from pubs.stores
[解决办法]
楼上正解。
套用一下：
关系社会，本来就是靠关系，不靠能力，
硬来即使串上去，成活率也会奇低。
[解决办法]
如果是2005，可以使用ROW_NUMBER(),2000好象只能借助临时表！
