日期:2014-05-18  浏览次数:20866 次

如何用SELECT 来自动生成一列序号
如,有一表  
select   col1,col2   from   tb1,
------------------
col1   col2
a             adf
b             asdfasa
c             adsdf
d             asd112
-----------------
想得到如下的结果,SQL   指令应如何写
t1   col1   col2
001       a             adf
002       b             asdfasa
003       c             adsdf
004       d             asd112


------解决方案--------------------
select id=identity(int,1,1),col1,col2 into # from tb1
select * from #
------解决方案--------------------
select id=right(identity(int,1,1)+10000,4),col1,col2 into # from tb1
select * from #
------解决方案--------------------
2005用
SELECT ROW_NUMBER() OVER (order by col2 ) AS COL,col1,col2 from tb1
2000用
select col= identity(int,1,1),col1,col2 into # from tbl
select * from #
drop table #
------解决方案--------------------
select id=identity(int,1,1),col1,col2 into # from tb1
select id=right(id+10000,4),col1,col2 from #