数据库篇之存储过程[bsp_getfavoriteproductlist]（非官方版本）

最新推荐文章于 2021-08-07 08:30:44 发布

大伟先生

最新推荐文章于 2021-08-07 08:30:44 发布

阅读量683

点赞数

CC 4.0 BY-SA版权

分类专栏： BrnShop

本文链接：https://blog.youkuaiyun.com/wenviwu/article/details/43049407

BrnShop 专栏收录该内容

15 篇文章

订阅专栏

本文介绍了一种使用SQL实现的分页查询优化方法，通过在存储过程中采用子查询和TOP子句结合的方式，实现了高效地获取指定用户收藏的商品列表。此方法避免了全表扫描，减少了每次分页查询的数据量。

差异如图：

更新语句如下：

DROP PROCEDURE [bsp_getfavoriteproductlist]
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [bsp_getfavoriteproductlist]
@pagesize int,
@pagenumber int,
@uid int
AS
BEGIN
	SET NOCOUNT ON;
	
	IF @pagenumber=1
	BEGIN
		SELECT [temp1].[recordid],[temp1].[uid],[temp1].[state],[temp1].[addtime],[temp1].[pid],[temp2].[name],[temp2].[shopprice],[temp2].[showimg] FROM
		(SELECT TOP(@pagesize) [recordid],[uid],[pid],[state],[addtime] FROM [bsp_favorites] WHERE [uid]=@uid ORDER BY [recordid] DESC) AS [temp1]
		LEFT JOIN [bsp_products] AS [temp2]
		ON [temp1].[pid]=[temp2].[pid]
	END
	ELSE
	BEGIN
		SELECT [temp1].[recordid],[temp1].[uid],[temp1].[state],[temp1].[addtime],[temp1].[pid],[temp2].[name],[temp2].[shopprice],[temp2].[showimg] FROM
		(SELECT TOP(@pagesize) [recordid],[uid],[pid],[state],[addtime] FROM [bsp_favorites] WHERE [uid]=@uid AND [pid] NOT IN
		(SELECT TOP(@pagesize*(@pagenumber-1)) [pid] FROM [bsp_favorites] WHERE [uid]=@uid ORDER BY [recordid] DESC) ORDER BY [recordid] DESC) AS [temp1]
		LEFT JOIN [bsp_products] AS [temp2]
		ON [temp1].[pid]=[temp2].[pid]
	END
	
END
GO