identity 仅当 SELECT 语句中有 INTO 子句时,才能使用 IDENTITY 函数。
informix?可以 select __NewSheetID,identity(int,1,1) as Serialid,goodsid into tmp from ..(假设goodsid从其他表来) where ... insert orderitem (SheetID,Serialid,goodsid) select * from tmpdrop table tmp
--这样: declare @orderitem table(SheetID varchar(10),Serialid int,goodsid varchar(10))--用下面的语句插入自动生成Serialid insert into @orderitem select 'dj0002',isnull(max(serialid),0)+1,'sp001' from @orderitem a where SheetID='dj0002' and goodsid<'sp001' --插入'dj0002','sp001'insert into @orderitem select 'dj0002',isnull(max(serialid),0)+1,'sp003' from @orderitem a where SheetID='dj0002' and goodsid<'sp003' --插入'dj0002','sp003'--查看结果 select * from @orderitem
3楼的方法刚试了。好象不行啊.在建临时表的时候就报错了啊.select __NewSheetID,identity(int,1,1) as Serialid,goodsid into tmp from ..(假设goodsid从其他表来) where ... into tmp ;将临时表建在这里就不报错了。但是identity(int,1,1) 又报错.
select __NewSheetID,identity(int,1,1) as Serialid,goodsid into tmp
from ..(假设goodsid从其他表来)
where ... insert orderitem (SheetID,Serialid,goodsid) select * from tmpdrop table tmp
--这样:
declare @orderitem table(SheetID varchar(10),Serialid int,goodsid varchar(10))--用下面的语句插入自动生成Serialid
insert into @orderitem select 'dj0002',isnull(max(serialid),0)+1,'sp001'
from @orderitem a where SheetID='dj0002' and goodsid<'sp001' --插入'dj0002','sp001'insert into @orderitem select 'dj0002',isnull(max(serialid),0)+1,'sp003'
from @orderitem a where SheetID='dj0002' and goodsid<'sp003' --插入'dj0002','sp003'--查看结果
select * from @orderitem
from ..(假设goodsid从其他表来)
where ...
into tmp ;将临时表建在这里就不报错了。但是identity(int,1,1) 又报错.