to zhuzhichao(竹之草): 关键是我的次序不一定是按ID呀,我要对某些字段进行排序,我按照某字段排序,然后进行翻页。
你不能在生成#OWOA_Users_Temp 之前排序嗎?
例如:sp_dboption 'dbname','select into/bulkcopy',true; go select UserName, FullName, Description into #Temp1 FROM dbo.OWOA_Users Order by UserName; go SELECT IDENTITY (int, 1, 1) ID_Num, UserName, FullName, Description into #OWOA_Users_Temp FROM #Temp1; go sp_dboption 'dbname','select into/bulkcopy',false; go
不是這樣效率高一些 因為用identity函數的select into 語句後面不能有order by 如果你不用order by的話 再生成#OWOA_Users_Temp後 select * from #OWOA_Users_Temp order by ... 那麼ID_Num在select出來的時候就不是連續的. 所有用Where ID_Num>xx and ID_Num<yy 來翻頁就做不到了,你說是嗎?因此我用另外一個臨時表#Temp1來過渡一下. 以達到#OWOA_Users_Temp表中既按UserName來排序ID_Num又是連續的作用.
不好意思,sql server的问题我就没机会发表意见了
那Oracle 呢?三千尽可说说。
再问zhuzhichao(竹之草): SELECT IDENTITY (int, 1, 1) ID_Num, UserName, FullName, Description into #OWOA_Users_Temp FROM #Temp1 如果我OWOA_Users表中有一个UserID字段是主键,怎么办?即如下: SELECT IDENTITY (int, 1, 1) ID_Num,UserID,UserName, FullName, Description into #OWOA_Users_Temp FROM #Temp1 这样就出错,怎么解决?
Cannot add identity column, using the SELECT INTO statement, to table '#Table_0', which already has column 'UserID' that inherits the identity property. 出现的是如上错误,请问怎么去掉这种继承的关系?"which already has column 'UserID' that inherits the identity property. "
你仍旧可以使用间接的方法做 SELECT UserID,UserName, FullName, Description into #Temp from ... 然后 SELECT IDENTITY (int, 1, 1) ID_Num,UserID,UserName, FullName, Description into #OWOA_Users_Temp from #temp
只要Where ID>xx and ID<yy
关键是我的次序不一定是按ID呀,我要对某些字段进行排序,我按照某字段排序,然后进行翻页。
之前排序嗎?
go
select UserName, FullName, Description
into #Temp1
FROM dbo.OWOA_Users Order by UserName;
go
SELECT IDENTITY (int, 1, 1) ID_Num, UserName, FullName, Description
into #OWOA_Users_Temp
FROM #Temp1;
go
sp_dboption 'dbname','select into/bulkcopy',false;
go
因為用identity函數的select into 語句後面不能有order by 如果你不用order by的話
再生成#OWOA_Users_Temp後
select * from #OWOA_Users_Temp order by ...
那麼ID_Num在select出來的時候就不是連續的.
所有用Where ID_Num>xx and ID_Num<yy
來翻頁就做不到了,你說是嗎?因此我用另外一個臨時表#Temp1來過渡一下.
以達到#OWOA_Users_Temp表中既按UserName來排序ID_Num又是連續的作用.
SELECT IDENTITY (int, 1, 1) ID_Num, UserName, FullName, Description
into #OWOA_Users_Temp
FROM #Temp1
如果我OWOA_Users表中有一个UserID字段是主键,怎么办?即如下:
SELECT IDENTITY (int, 1, 1) ID_Num,UserID,UserName, FullName, Description
into #OWOA_Users_Temp
FROM #Temp1
这样就出错,怎么解决?
出现的是如上错误,请问怎么去掉这种继承的关系?"which already has column 'UserID' that inherits the identity property.
"
SELECT UserID,UserName, FullName, Description
into #Temp from ...
然后
SELECT IDENTITY (int, 1, 1) ID_Num,UserID,UserName, FullName, Description
into #OWOA_Users_Temp from #temp
你的UserID是個
identity的列再做報表的時候將它select出來是沒有意義的.
有意义,我的UserID是个唯一的值标示每一条记录,UserID不一定是连续的,