--上面是针对原表中无主键(或不重复的字段)的情况,如果你的表中有主键,可以用:select col1=(select sum(1) from 原表 where 主键<=a.主键),表中的原有列1,表中的原有列2 from 原表 a order by 主键
http://expert.csdn.net/Expert/topic/2437/2437014.xml?temp=.67857 交流]自增号1: 自增列 类型为:int identity(1,1) 当然也可以是bigint,smallint eg: create table tbName(id int identity(1,1),description varchar(20)) 或在用企业管理器设计表字段时,将字段设为int,将标识设为是,其它用默认即可2: 查询时加序号: a:没有主键的情形: Select identity(int,1,1) as iid,* into #tmp from TableName Select * from #tmp Drop table #tmp b:有主键的情形: Select (Select sum(1) from TableName where KeyField <= a.KeyField) as iid,* from TableName a
select col = identity(int,1,1) ,col1,col2 into #t from 表
用臨時表.增加indentity欄位即可
select IDENTITY(int, 1,1) AS ID_Num,* into #temp from 表 select * from #temp
--根据作者意图,实现如下:IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'T1') DROP TABLE T1 GOCREATE TABLE T1 ( column_1 int, column_2 varchar(30))GOINSERT T1 SELECT 1, 'Row #1' UNION ALL SELECT 4, 'Row #2' UNION ALL SELECT 12, 'Row #3' UNION ALL SELECT 53, 'Row #4'GOALTER TABLE T1 ADD col1 INT IDENTITYGO--最终查询语句:SELECT col1,column_1,column_2 FROM T1SELECT COUNT(*)FROM T1--结果集:col1 column_1 column_2 ----------- ----------- --------- 1 1 Row #1 2 4 Row #2 3 12 Row #3 4 53 Row #4(所影响的行数为 4 行) ----------- 4(所影响的行数为 1 行)--删除测试环境DROP TABLE T1
select (select sum(1) from 原表 where 有顺序的可以唯一标识一条记录的列<=tem.有顺序的可以唯一标识一条记录的列) col1,* from 原表 tem
select IDENTITY(int, 1,1) AS ID_Num,* into #temp from 表 select * from #temp
据说 select (select count(*) from 原表 where 有顺序的可以唯一标识一条记录的列或列的组合<=tem.有顺序的可以唯一标识一条记录的列或列的组合) col1,* from 原表 tem 效率更高。
交流]自增号1: 自增列 类型为:int identity(1,1) 当然也可以是bigint,smallint
eg: create table tbName(id int identity(1,1),description varchar(20))
或在用企业管理器设计表字段时,将字段设为int,将标识设为是,其它用默认即可2: 查询时加序号:
a:没有主键的情形:
Select identity(int,1,1) as iid,* into #tmp from TableName
Select * from #tmp
Drop table #tmp
b:有主键的情形:
Select (Select sum(1) from TableName where KeyField <= a.KeyField) as iid,* from TableName a
select * from #temp
WHERE TABLE_NAME = 'T1')
DROP TABLE T1
GOCREATE TABLE T1 ( column_1 int, column_2 varchar(30))GOINSERT T1 SELECT 1, 'Row #1'
UNION ALL SELECT 4, 'Row #2'
UNION ALL SELECT 12, 'Row #3'
UNION ALL SELECT 53, 'Row #4'GOALTER TABLE T1 ADD col1 INT IDENTITYGO--最终查询语句:SELECT col1,column_1,column_2 FROM T1SELECT COUNT(*)FROM T1--结果集:col1 column_1 column_2
----------- ----------- ---------
1 1 Row #1
2 4 Row #2
3 12 Row #3
4 53 Row #4(所影响的行数为 4 行)
-----------
4(所影响的行数为 1 行)--删除测试环境DROP TABLE T1
select * from #temp
select (select count(*) from 原表 where 有顺序的可以唯一标识一条记录的列或列的组合<=tem.有顺序的可以唯一标识一条记录的列或列的组合) col1,* from 原表 tem
效率更高。