首先请各位注意:不是求具体的分页sql语句。
我现在面临的工作是:进行一个“音乐搜索”,要能进行分页查询,列表下方有常规的"下一页",“上一页”,“共有XX条数据符合要求”这3个功能。就在这个“共有XX条数据符合要求”问题上,我遇到了困难,我现在的做法是:先将查询结果放进一个临时表中,使用count(*)统计符合搜索结果的总数,再对这个临时表进行一个分页查询。但这样做肯定有违背分页查询的精神的,甚至可以说比不用分页性能更低一点,因为始终要全部查询了一次(也就是查询并生产临时表的过程),这种做法实际上就有3此查询了(分别为:查询全部数据并生成临时表,查询临时表总数,对临时表进行分页查询)
有没有简便的SQL方案,能在执行分页查询的时候同时得出符合查询条件的总数?谢谢!
我现在面临的工作是:进行一个“音乐搜索”,要能进行分页查询,列表下方有常规的"下一页",“上一页”,“共有XX条数据符合要求”这3个功能。就在这个“共有XX条数据符合要求”问题上,我遇到了困难,我现在的做法是:先将查询结果放进一个临时表中,使用count(*)统计符合搜索结果的总数,再对这个临时表进行一个分页查询。但这样做肯定有违背分页查询的精神的,甚至可以说比不用分页性能更低一点,因为始终要全部查询了一次(也就是查询并生产临时表的过程),这种做法实际上就有3此查询了(分别为:查询全部数据并生成临时表,查询临时表总数,对临时表进行分页查询)
有没有简便的SQL方案,能在执行分页查询的时候同时得出符合查询条件的总数?谢谢!
SELECT
rownum=ROW_NUMBER()OVER(ORDER BY InfoId)
,[InfoId]
,[Abstract]
FROM Bull as a
)
select
t.[InfoId]
,t.[Abstract]
,b.rownum as TotalRow
from t cross join (select top 1 rownum from t order by t.rownum desc) as b
WHERE t.rownum BETWEEN @PageRow*(@PageNo-1)+1
AND @PageRow*@PageNo
create proc getMusicList
@PageIndex int,
@PageLength int,
@Total int output
as
SELECT ROW_NUMBER() OVER(Music.ID) AS rownum,Music.Name
INTO #temp
FROM Music
SELECT @Total=COUNT(*) FROM #temp
select * from #temp where rownum>((@PageIndex-1)*@PageLength) and rownum<=(@PageIndex*@PageLength)
DROP TABLE #temp我现在这样做是先将所有符合查询条件的数据查询到临时表,再取总数,再对临时表分页。但有什么更高性能的方法?
+ CAST(@PageSize AS VARCHAR)+') FROM (Select * FROM ' + @TableName + @new_where1+') AS T'
IF @RecorderCount = 0
BEGIN
EXEC SP_EXECUTESQL @SqlCount,N'@TotalCount INT OUTPUT,@TotalPageCount INT OUTPUT',
@TotalCount OUTPUT,@TotalPageCount OUTPUT
END
ELSE
BEGIN
Select @TotalCount = @RecorderCount
END
IF @PageIndex > CEILING((@TotalCount+0.0)/@PageSize)
BEGIN
SET @PageIndex = CEILING((@TotalCount+0.0)/@PageSize)
END
IF @PageIndex = 1 or @PageIndex >= CEILING((@TotalCount+0.0)/@PageSize)
BEGIN
IF @PageIndex = 1 --返回第一页数据
BEGIN
SET @Sql = 'Select * FROM (Select TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM '
+ @TableName + @new_where1 + @new_order1 +') AS TMP ' + @new_order1
END
IF @PageIndex >= CEILING((@TotalCount+0.0)/@PageSize) --返回最后一页数据
BEGIN
SET @Sql = 'Select TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM ('
+ 'Select TOP ' + STR(ABS(@PageSize*@PageIndex-@TotalCount-@PageSize))
+ ' ' + @FieldList + ' FROM '
+ @TableName + @new_where1 + @new_order2 + ' ) AS TMP '
+ @new_order1
END
END
ELSE
BEGIN
IF @SortType = 1 --仅主键正序排序
BEGIN
IF @PageIndex <= CEILING((@TotalCount+0.0)/@PageSize)/2 --正向检索
BEGIN
SET @Sql = 'Select TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM '
+ @TableName + @new_where2 + @PrimaryKey + ' > '
+ '(Select MAX(' + @PrimaryKey + ') FROM (Select TOP '
+ STR(@PageSize*(@PageIndex-1)) + ' ' + @PrimaryKey
+ ' FROM ' + @TableName
+ @new_where1 + @new_order1 +' ) AS TMP) '+ @new_order1
END
ELSE --反向检索
BEGIN
SET @Sql = 'Select TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM ('
+ 'Select TOP ' + STR(@PageSize) + ' '
+ @FieldList + ' FROM '
+ @TableName + @new_where2 + @PrimaryKey + ' < '
+ '(Select MIN(' + @PrimaryKey + ') FROM (Select TOP '
+ STR(@TotalCount-@PageSize*@PageIndex) + ' ' + @PrimaryKey
+ ' FROM ' + @TableName
+ @new_where1 + @new_order2 +' ) AS TMP) '+ @new_order2
+ ' ) AS TMP ' + @new_order1
END
END
IF @SortType = 2 --仅主键反序排序
BEGIN
IF @PageIndex <= CEILING((@TotalCount+0.0)/@PageSize)/2 --正向检索
BEGIN
SET @Sql = 'Select TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM '
+ @TableName + @new_where2 + @PrimaryKey + ' < '
+ '(Select MIN(' + @PrimaryKey + ') FROM (Select TOP '
+ STR(@PageSize*(@PageIndex-1)) + ' ' + @PrimaryKey
+' FROM '+ @TableName
+ @new_where1 + @new_order1 + ') AS TMP) '+ @new_order1
END
ELSE --反向检索
BEGIN
SET @Sql = 'Select TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM ('
+ 'Select TOP ' + STR(@PageSize) + ' '
+ @FieldList + ' FROM '
+ @TableName + @new_where2 + @PrimaryKey + ' > '
+ '(Select MAX(' + @PrimaryKey + ') FROM (Select TOP '
+ STR(@TotalCount-@PageSize*@PageIndex) + ' ' + @PrimaryKey
+ ' FROM ' + @TableName
+ @new_where1 + @new_order2 +' ) AS TMP) '+ @new_order2
+ ' ) AS TMP ' + @new_order1
END
END
IF @SortType = 3 --多列排序,必须包含主键,且放置最后,否则不处理
BEGIN
IF CHARINDEX(',' + @PrimaryKey + ' ',',' + @Order) = 0
BEGIN PRINT('ERR_02') RETURN END
IF @PageIndex <= CEILING((@TotalCount+0.0)/@PageSize)/2 --正向检索
BEGIN
SET @Sql = 'Select TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM ( '
+ 'Select TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM ( '
+ ' Select TOP ' + STR(@PageSize*@PageIndex) + ' ' + @FieldList
+ ' FROM ' + @TableName + @new_where1 + @new_order1 + ' ) AS TMP '
+ @new_order2 + ' ) AS TMP ' + @new_order1
END
ELSE --反向检索
BEGIN
SET @Sql = 'Select TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM ( '
+ 'Select TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM ( '
+ ' Select TOP ' + STR(@TotalCount-@PageSize *@PageIndex+@PageSize) + ' ' + @FieldList
+ ' FROM ' + @TableName + @new_where1 + @new_order2 + ' ) AS TMP '
+ @new_order1 + ' ) AS TMP ' + @new_order1
END
END
END
PRINT(@SQL)
EXEC(@Sql) [/code]