--建立测试环境
create table tb(id int,list varchar(10))
insert tb(id,list)
select '1','国内' union all
select '2','国外' union all
select '3','国内' union all
select '4','国外' union all
select '5','国外' union all
select '6','国内' union all
select '7','国外' union all
select '8','国内' union all
select '9','国内' union all
select '10','国内' union all
select '11','国外'
go
--分页定义表
CREATE TABLE tb_Page(
list varchar(10) PRIMARY KEY, --类别名称,与tb表的list关联
Records int, --每页显示的记录数
Orders int) --在页中的显示顺序
INSERT tb_Page SELECT '国内',3,1
UNION ALL SELECT '国外',3,2
GO
--实现分页处理的存储过程
CREATE PROC p_PageView
@PageCurrent int=1 --要显示的当前页码
AS
SET NOCOUNT ON
--得到每页的记录数
DECLARE @PageSize int
SELECT @PageSize=SUM(Records) FROM tb_Page
IF ISNULL(@PageSize,0)<0 RETURN
--分页显示处理
SET @PageCurrent=@PageCurrent*@PageSize
SET ROWCOUNT @PageCurrent
SELECT SID=IDENTITY(int,1,1),ID
INTO # FROM(
SELECT TOP 100 PERCENT a.ID
FROM tb a
LEFT JOIN tb_Page b ON a.list=b.list
ORDER BY CASE WHEN b.list IS NULL THEN 1 ELSE 0 END,--分类没有定义的显示在最后
((SELECT COUNT(*) FROM tb
WHERE list=a.list
AND (id<a.id OR id=a.id AND id<=a.id))-1)
/b.Records,
b.Orders,a.ID )a
IF @PageCurrent>@PageSize
BEGIN
SET @PageCurrent=@PageCurrent-@PageSize
SET ROWCOUNT @PageCurrent
DELETE FROM #
END
SELECT a.* FROM tb a,# b
WHERE a.ID=b.ID
ORDER BY b.SID
GO
--调用
EXEC p_PageView 1
go
--删除测试环境
drop table tb ,tb_page
drop proc p_PageView
go
/*--测试结果
id list
----------- ----------
1 国内
3 国内
6 国内
2 国外
4 国外
5 国外
--创建测试环境
Create Table A
(id Int,
list Nvarchar(20))
--插入数据
Insert A Select 1, N'国内'
Union All Select 2, N'国外'
Union All Select 3, N'国内'
Union All Select 4, N'国外'
Union All Select 5, N'国外'
Union All Select 6, N'国内'
Union All Select 7, N'国外'
Union All Select 8, N'国内'
Union All Select 9, N'国内'
Union All Select 10, N'国内'
Union All Select 11, N'国外'
GO
--创建存储过程
Create ProceDure SP_TEST(@PageCount Int)
As
Begin
Declare @S Varchar(8000)
Select @S = 'Select * From (Select TOP 3 * From A Where list = N''国内'' And ID Not In (Select TOP ' + Cast(3 * (@PageCount - 1) As Varchar) + ' ID From A Where list = N''国内'' Order By ID) Order By ID) A
Union All
Select * From (Select TOP 3 * From A Where list = N''国外'' And ID Not In (Select TOP ' + Cast(3 * (@PageCount - 1) As Varchar) + ' ID From A Where list = N''国外'' Order By ID) Order By ID) A'
EXEC(@S)
End
GO
--测试
--第一页
EXEC SP_TEST 1
--第二页
EXEC SP_TEST 2
GO
--删除测试环境
Drop Table A
Drop ProceDure SP_TEST
--结果
/*
--第一页结果
id list
1 国内
3 国内
6 国内
2 国外
4 国外
5 国外
写个分页存储过程
create proc pShowPerPage
(
@intIndex int
)
as
begin
思路:
1,select top indIndex*3 order by id
2, slect top 3 * from (step 1) order by id desc
3,使用动态SQL
end