123.根据分类实现的分页存储过程

系统管理员 2024-02-19 19:05 40阅读 0赞

--要分页的原始数据
    CREATE TABLE tb(
    ID    int PRIMARY KEY,  --记录编号
    grade  varchar(10),     --类别名称
    uptime datetime)        --更新时间
    INSERT tb SELECT 1 ,'a','2004-12-11'
    UNION ALL SELECT 2 ,'b','2004-12-11'
    UNION ALL SELECT 3 ,'c','2004-12-11'
    UNION ALL SELECT 4 ,'a','2004-12-12'
    UNION ALL SELECT 5 ,'c','2004-12-13'
    UNION ALL SELECT 6 ,'c','2004-12-13'
    UNION ALL SELECT 7 ,'a','2004-12-14'
    UNION ALL SELECT 8 ,'a','2004-12-15'
    UNION ALL SELECT 9 ,'b','2004-12-16'
    UNION ALL SELECT 10,'b','2004-12-17'
    UNION ALL SELECT 11,'a','2004-12-17'
    
    --分页定义表
    CREATE TABLE tb_Page(
    grade   varchar(10) PRIMARY KEY, --类别名称,与tb表的grade关联
    Records int,                     --每页显示的记录数
    Orders  int)                     --在页中的显示顺序
    INSERT tb_Page SELECT 'c',2,1
    UNION  ALL     SELECT 'b',1,2
    UNION  ALL     SELECT 'a',2,3
    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.grade=b.grade
    	ORDER BY CASE WHEN b.grade IS NULL THEN 1 ELSE 0 END,--分类没有定义的显示在最后
    		((SELECT COUNT(*) FROM tb 
    			WHERE grade=a.grade 
    				AND (uptime>a.uptime OR uptime=a.uptime AND id>=a.id))-1)
    		/b.Records,
    		b.Orders,a.ID DESC)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 2
    /*--结果
    ID          grade      uptime                                                 
    ----------- ---------- ------------------------------------------------------ 
    3           c          2004-12-11 00:00:00.000
    9           b          2004-12-16 00:00:00.000
    7           a          2004-12-14 00:00:00.000
    4           a          2004-12-12 00:00:00.000
    2           b          2004-12-11 00:00:00.000
    --*/