代码如下：
- SQL code
-
DECLARE @TotalPage int
SELECT @TotalPage=Count(Identifier) FROM View_BbsInfo WHERE
(@Identifier IS NULL OR Identifier=@Identifier)AND
(@IsTop IS NULL OR IsTop=@IsTop)AND
(@IsPlacard IS NULL OR IsPlacard=@IsPlacard)
IF(@TotalPage%@PageSize=0)
BEGIN
SET @TotalPage=@TotalPage/@PageSize
END
ELSE
BEGIN
SET @TotalPage=Round(@TotalPage/@PageSize,0)+1
END
SELECT TOP (@PageSize) Identifier,IsTop,IsPlacard,@TotalPage as totalPage FROM View_BbsInfo WHERE
Identifier NOT IN (SELECT Top (@PageSize*(@CurrentPage-1))Identifier FROM View_BbsInfo WHERE
(@Identifier IS NULL OR Identifier=@Identifier)AND
(@IsTop IS NULL OR IsTop=@IsTop)AND
(@IsPlacard IS NULL OR IsPlacard=@IsPlacard))
AND
(@Identifier IS NULL OR Identifier=@Identifier)AND
(@IsTop IS NULL OR IsTop=@IsTop)AND
(@IsPlacard IS NULL OR IsPlacard=@IsPlacard)
优化后，我首先利用临时表将条件查询的所有结果存储起来，然后再分页查询该临时表
代码如下：
- SQL code
-
--查询结果到临时表
SELECT TOP (@PageSize) Identifier,IsTop,IsPlacard INTO #tempTable FROM View_BbsInfo WHERE
(@Identifier IS NULL OR Identifier=@Identifier)AND
(@IsTop IS NULL OR IsTop=@IsTop)AND
(@IsPlacard IS NULL OR IsPlacard=@IsPlacard)
DECLARE @TotalPage int
SELECT @TotalPage=Count(Identifier) FROM #tempTable
IF(@TotalPage%@PageSize=0)
BEGIN
SET @TotalPage=@TotalPage/@PageSize
END
ELSE
BEGIN
SET @TotalPage=Round(@TotalPage/@PageSize,0)+1
END
SELECT TOP (@PageSize) Identifier,IsTop,IsPlacard,@TotalPage as totalPage FROM #tempTable WHERE
Identifier NOT IN (SELECT Top (@PageSize*(@CurrentPage-1))Identifier FROM #tempTable
问题来了，没进行测试，不知道哪种的性能更好些？
尽管减少了三次查询的过程，但是建立临时表的过程依然需要插入、查询操作，感觉性能的确不好确定
希望各位帮忙解释一下哪种比较好？