因为该函数在表中创建一个列,所以必须用下列方式中的一种在选择列表中指定该列的名称: --(1) SELECT IDENTITY(int, 1,1) AS ID_Num INTO NewTable FROM OldTable--(2) SELECT ID_Num = IDENTITY(int, 1, 1) INTO NewTable FROM OldTable
你可以这样,假设某字段col唯一. --sql 2000 select id = (select count(1) from tb where col < t.col) + 1 from tb--sql 2005 select id = row_number() over(partition by col) from tb
你可以这样,假设某字段col唯一. --sql 2000 select id = (select count(1) from tb where col < t.col) + 1 from tb--sql 2005 select id = row_number() over(order by col) from tb如果是多个字段,可以这样: --sql 2000 select id = (select count(1) from tb where (c1 < t.c1) or (c1 = t.c1 and c2 < t.c2)) + 1 from tb--sql 2005 select id = row_number() over(order by c1 , c2) from tb依此类推.
with cte as( select *,id=row_number() over(order by getdate())from tb) --相当于增加一个虚拟的自增列select********
--如果是2005的话,可以使用output --参考例子! create proc addnewcreditcard @cardtype nvarchar(50), @cardnumber nvarchar(25), @expmonth tinyint, @expyear smallint as declare @username varchar(60) declare @loginame varchar(60) declare @creditcardinfo table(creditcardid int) --这里创建一个临时表,存放creditcardid declare @creditcardid int set @loginame = suser_name() set @username = user_name()begin transaction insert sales.creditcard(cardtype,cardnumber,expmonth,expyear) output inserted.creditcardid --这里的output inserted.很关键,就是得到刚插入的id ,类似于@@idenity into @creditcardinfo --这里这时insert into @creditcardinfo values (@cardtype,@cardnumber,@expmonth,@expyear);set @creditcardid= (select creditcardid from @creditcardinfo)raiserror(60001,10,1,@creditcardid,@loginame,@username) commit transaction; go output --返回受 INSERT、UPDATE、DELETE 或 MERGE 语句影响的各行中的信息,或返回基于受这些语句影响的各行的表达式。这些结果可以返回到处理应用程序,以供在确认消
2005以上可以用row_number()
select row_number() over(order by getdate()) id from tb
应该怎么解决
--(1)
SELECT IDENTITY(int, 1,1) AS ID_Num
INTO NewTable
FROM OldTable--(2)
SELECT ID_Num = IDENTITY(int, 1, 1)
INTO NewTable
FROM OldTable
--sql 2000
select id = (select count(1) from tb where col < t.col) + 1 from tb--sql 2005
select id = row_number() over(partition by col) from tb
--sql 2000
select id = (select count(1) from tb where col < t.col) + 1 from tb--sql 2005
select id = row_number() over(order by col) from tb如果是多个字段,可以这样:
--sql 2000
select id = (select count(1) from tb where (c1 < t.c1) or (c1 = t.c1 and c2 < t.c2)) + 1 from tb--sql 2005
select id = row_number() over(order by c1 , c2) from tb依此类推.
with cte as(
select *,id=row_number() over(order by getdate())from tb) --相当于增加一个虚拟的自增列select********
--参考例子!
create proc addnewcreditcard
@cardtype nvarchar(50),
@cardnumber nvarchar(25),
@expmonth tinyint,
@expyear smallint
as
declare @username varchar(60)
declare @loginame varchar(60)
declare @creditcardinfo table(creditcardid int) --这里创建一个临时表,存放creditcardid
declare @creditcardid int
set @loginame = suser_name()
set @username = user_name()begin transaction
insert sales.creditcard(cardtype,cardnumber,expmonth,expyear)
output inserted.creditcardid --这里的output inserted.很关键,就是得到刚插入的id ,类似于@@idenity
into @creditcardinfo --这里这时insert into @creditcardinfo
values (@cardtype,@cardnumber,@expmonth,@expyear);set @creditcardid= (select creditcardid from @creditcardinfo)raiserror(60001,10,1,@creditcardid,@loginame,@username)
commit transaction;
go
output
--返回受 INSERT、UPDATE、DELETE 或 MERGE 语句影响的各行中的信息,或返回基于受这些语句影响的各行的表达式。这些结果可以返回到处理应用程序,以供在确认消