SQL Server单条sql实现分页和总行数

最新推荐文章于 2024-05-29 17:06:06 发布

weixin_34236497

最新推荐文章于 2024-05-29 17:06:06 发布

阅读量554

点赞数

CC 4.0 BY-SA版权

文章标签：数据库 python java

原文链接：https://my.oschina.net/shma1664/blog/709567

本文介绍了一种在SQLServer中使用单条SQL语句实现分页查询的方法，并展示了如何同时获取总记录数。该方法利用了ROW_NUMBER()和COUNT()窗口函数来有效地进行分页操作。

2019独角兽企业重金招聘Python工程师标准>>>

SQL Server单条sql实现分页和总行数

SELECT
	CustomerId AS customerId,
	Phone AS userMobile,
	Name AS userName,
	FirstOrderTime AS firstOrderTime,
	LatestOrderTime AS lastestOrderTime,
	CustomerLevel AS customerLevel,
	Call400Count AS call400Count,
	OnlineOrderCount AS onlineOrderCount,
	CityId AS cityId,
	CityName AS cityName,
	total AS totalNum
FROM
	(
		SELECT
			CustomerId,
			Phone,
			Name,
			FirstOrderTime,
			LatestOrderTime,
			CustomerLevel,
			Call400Count,
			OnlineOrderCount,
			CityId,
			CityName,
			ROW_NUMBER () OVER (ORDER BY CustomerId DESC) rn,
			COUNT (1) OVER () AS total
		FROM
			CustomerInfo WITH (NOLOCK)
		WHERE
			1 = 1
	) AS t
WHERE
	1 = 1
AND t.rn BETWEEN 1
AND 20

转载于:https://my.oschina.net/shma1664/blog/709567