另外,楼主也可以考虑这样用临时表if object_id('testdb') is not null drop table testdb select ID=identity(int, 1, 1), * into testdb from tablename select * from #T drop table testdb这样,就不会存在中途退出程序临时表没有删掉的情况
另外,楼主也可以考虑这样用临时表if object_id('testdb') is not null drop table testdb select ID=identity(int, 1, 1), * into testdb from tablename select * from testdb drop table testdb这样,就不会存在中途退出程序临时表没有删掉的情况
将实表用作临时表用, 多个进程访问时会混乱,很有问题. "如果数据量比较大,如10万条,这样的操作是否很占用系统资源" 临时表也是表,如果说资源占用的话,你就当是考滤将一个10w记录的表复制到另一个表里, 自己想吧.你做分页用的话,不一定要用 row_number之类的东西,标识列+ top n 或 count已经能解决问题了.
select top 3 [News].[Unique], [News].[Title], [News].[Create] from [News] where [News].[Unique] not in ( select top 30 [News].[Unique] from [News] order by [News].[Create] desc ) order by [News].[Create] desc是这样吗?第11页的效果。
CREATE PROCEDURE SP_Page @TB VARCHAR(50), @COL VARCHAR(50), @ORDERBY BIT, @COLLIST VARCHAR(800), @PAGESIZE INT, @PAGE INT, @CONDITION VARCHAR(800), @RecPages INT, @RecCount INT OUTPUT, @PAGES INT OUTPUT, @OUTSQL NVARCHAR(4000) OUTPUTASDECLARE @SQL NVARCHAR(4000) DECLARE @WHERE1 VARCHAR(800) DECLARE @WHERE2 VARCHAR(800)IF @CONDITION IS NULL OR RTRIM(@CONDITION) = '' BEGIN SET @WHERE1=' WHERE ' SET @WHERE2=' ' END ELSE BEGIN SET @WHERE1=' WHERE ('+@CONDITION+') AND ' SET @WHERE2=' WHERE ('+@CONDITION+') ' ENDSET @SQL = 'SELECT @RecCount=COUNT(*),@PAGES=CEILING((+0.0)/'+CAST(@PAGESIZE AS VARCHAR)+') FROM '+@TB+@WHERE2IF @RecPages = 0 EXEC SP_EXECUTESQL @SQL,N'@PAGES INT OUTPUT,@RecCount INT OUTPUT',@PAGES OUTPUT,@RecCount OUTPUT ELSE SELECT @PAGES = @RecPagesIF @ORDERBY=0 SET @SQL= 'SELECT TOP '+CAST(@PAGESIZE AS VARCHAR)+' '+@COLLIST+' FROM '+@TB+@WHERE1+@COL+'>(SELECT MAX('+@COL+') '+' FROM (SELECT TOP '+CAST(@PAGESIZE*(@PAGE-1) AS VARCHAR)+' '+ @COL+' FROM '+@TB+@WHERE2+'ORDER BY '+@COL+') t) ORDER BY '+@COL ELSE SET @SQL='SELECT TOP '+CAST(@PAGESIZE AS VARCHAR)+' '+@COLLIST+' FROM '+@TB+@WHERE1+@COL+'<(SELECT MIN('+@COL+') '+' FROM (SELECT TOP '+CAST(@PAGESIZE*(@PAGE-1) AS VARCHAR)+' '+ @COL+' FROM '+@TB+@WHERE2+'ORDER BY '+@COL+' DESC) t) ORDER BY '+@COL+' DESC'IF @PAGE=1 SET @SQL='SELECT TOP '+CAST(@PAGESIZE AS VARCHAR)+' '+@COLLIST+' FROM '+@TB+@WHERE2+'ORDER BY '+@COL+CASE @ORDERBY WHEN 0 THEN '' ELSE ' DESC' ENDSET @OUTSQL = @SQLEXEC(@SQL)这是以前写的一个分页存储过程.设id为标识实际上就是 当当前页=1时 select top 页行数 from tb 当前页为n时 select top 页行数 from tb where id>(select max(id) from (select top (n-1)*页行数 id from tb) 比如当前页为5,每页显示10条记录 那么前4页应该显示40条记录, 若以id顺序来排的话, 则第5页显示的记录应该是 id大小排第41至第50的.即取前10条 id>( 前40条记录的最大id)这种分页算法,页数越靠前越快, 越靠后速度会变慢.
可以用臨時表
大师,就目前来看,您的方案好像改动比较小些。1、请问#T就是临时表,对吗?这个表在执行完毕后就会释放掉对吗?
2、使用临时表有没有需要注意的问题?(抱歉,我从未使用过临时表)
3、如果数据量比较大,如10万条,这样的操作是否很占用系统资源?万分感谢!!
drop table #T
select ID=identity(int, 1, 1), * into testdb from tablename
select * from #T
drop table testdb这样,就不会存在中途退出程序临时表没有删掉的情况
select ID=identity(int, 1, 1), * into testdb from tablename
select * from testdb
drop table testdb这样,就不会存在中途退出程序临时表没有删掉的情况
"如果数据量比较大,如10万条,这样的操作是否很占用系统资源"
临时表也是表,如果说资源占用的话,你就当是考滤将一个10w记录的表复制到另一个表里, 自己想吧.你做分页用的话,不一定要用 row_number之类的东西,标识列+ top n 或 count已经能解决问题了.
where [News].[Unique] not in
(
select top 30 [News].[Unique] from [News] order by [News].[Create] desc
)
order by [News].[Create] desc是这样吗?第11页的效果。
@COL VARCHAR(50),
@ORDERBY BIT,
@COLLIST VARCHAR(800),
@PAGESIZE INT,
@PAGE INT,
@CONDITION VARCHAR(800),
@RecPages INT,
@RecCount INT OUTPUT,
@PAGES INT OUTPUT,
@OUTSQL NVARCHAR(4000) OUTPUTASDECLARE @SQL NVARCHAR(4000)
DECLARE @WHERE1 VARCHAR(800)
DECLARE @WHERE2 VARCHAR(800)IF @CONDITION IS NULL OR RTRIM(@CONDITION) = ''
BEGIN
SET @WHERE1=' WHERE '
SET @WHERE2=' '
END
ELSE
BEGIN
SET @WHERE1=' WHERE ('+@CONDITION+') AND '
SET @WHERE2=' WHERE ('+@CONDITION+') '
ENDSET @SQL = 'SELECT @RecCount=COUNT(*),@PAGES=CEILING((+0.0)/'+CAST(@PAGESIZE AS VARCHAR)+') FROM '+@TB+@WHERE2IF @RecPages = 0
EXEC SP_EXECUTESQL @SQL,N'@PAGES INT OUTPUT,@RecCount INT OUTPUT',@PAGES OUTPUT,@RecCount OUTPUT
ELSE
SELECT @PAGES = @RecPagesIF @ORDERBY=0
SET @SQL= 'SELECT TOP '+CAST(@PAGESIZE AS VARCHAR)+' '+@COLLIST+' FROM '+@TB+@WHERE1+@COL+'>(SELECT MAX('+@COL+') '+' FROM (SELECT TOP '+CAST(@PAGESIZE*(@PAGE-1) AS VARCHAR)+' '+
@COL+' FROM '+@TB+@WHERE2+'ORDER BY '+@COL+') t) ORDER BY '+@COL
ELSE
SET @SQL='SELECT TOP '+CAST(@PAGESIZE AS VARCHAR)+' '+@COLLIST+' FROM '+@TB+@WHERE1+@COL+'<(SELECT MIN('+@COL+') '+' FROM (SELECT TOP '+CAST(@PAGESIZE*(@PAGE-1) AS VARCHAR)+' '+
@COL+' FROM '+@TB+@WHERE2+'ORDER BY '+@COL+' DESC) t) ORDER BY '+@COL+' DESC'IF @PAGE=1
SET @SQL='SELECT TOP '+CAST(@PAGESIZE AS VARCHAR)+' '+@COLLIST+' FROM '+@TB+@WHERE2+'ORDER BY '+@COL+CASE @ORDERBY WHEN 0 THEN '' ELSE ' DESC' ENDSET @OUTSQL = @SQLEXEC(@SQL)这是以前写的一个分页存储过程.设id为标识实际上就是
当当前页=1时
select top 页行数 from tb
当前页为n时
select top 页行数 from tb where id>(select max(id) from (select top (n-1)*页行数 id from tb)
比如当前页为5,每页显示10条记录
那么前4页应该显示40条记录, 若以id顺序来排的话, 则第5页显示的记录应该是 id大小排第41至第50的.即取前10条 id>( 前40条记录的最大id)这种分页算法,页数越靠前越快, 越靠后速度会变慢.