联表分页查询 OFFSET

最新推荐文章于 2023-09-18 22:29:43 发布

lmy_loveF

最新推荐文章于 2023-09-18 22:29:43 发布

阅读量324

点赞数

分类专栏： # SQL语句文章标签： sqlserver

本文链接：https://blog.csdn.net/lmy_loveF/article/details/125671937

版权

SQL语句专栏收录该内容

1 篇文章 0 订阅

订阅专栏

DECLARE @pageIndex INT= ?

DECLARE @pageSize INT= ?

正常的查询语句

OFFSET ( @pageIndex - 1 ) * @pageSize ROWS
							FETCH NEXT @pageSize ROWS ONLY


OFFSET 从第几条数据开始 ROWS
							FETCH NEXT @pageSize ROWS ONLY

一页10条数据，
第一页
OFFSET 0 ROWS
							FETCH NEXT  10 ROWS ONLY
第二页
OFFSET 10 ROWS
							FETCH NEXT  10 ROWS ONLY

DECLARE @pageIndex INT= ?
	DECLARE @pageSize INT= ?
	SELECT  ISNULL(uhbi_id, 0) AS uhbi_id ,
					ISNULL(ui_user_code, '') AS ui_user_code ,
					ISNULL(ui_user_name, '') AS ui_user_name ,
					ISNULL(uhbi_userid, '') AS uhbi_userid ,
					ISNULL(uhbi_balance_hours, 0) AS uhbi_balance_hours ,
					ISNULL(uhbi_holiday_type, '') AS uhbi_holiday_type ,
					ISNULL(ui_department_id, 0) AS ui_department_id ,
					ISNULL(ui_jop_status, 0) AS ui_jop_status ,
					ISNULL(ui_department_groupid, 0) AS ui_department_groupid ,
					ISNULL(CONVERT(VARCHAR(20), uhdi_edit_time, 20), '1970-01-01 00:00:00') AS uhdi_edit_time
	FROM    USER_HOLIDAY_BALANCE_INFO
					LEFT JOIN USER_INFO ON ui_userid = uhbi_userid
																 AND uhbi_holiday_type = 10
	WHERE   uhbi_holiday_type = 10
	ORDER BY ui_jop_status ,
					ui_department_id ,
					ui_department_groupid ,
					ui_user_code
					OFFSET ( @pageIndex - 1 ) * @pageSize ROWS
							FETCH NEXT @pageSize ROWS ONLY