通过SQL的select语句自动得到count语句和得到sql server 2005 的分页查询-优快云博客

本文介绍如何使用SQL Server 2005进行分页查询及获取记录总数的方法，并展示了针对不同复杂度的SQL查询语句如何进行分页与计数的实现。同时，提到了SQL中不支持直接使用UNION进行分页查询的情况及其解决方案。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

String sql = "select u.name from Users u order by u.id ";
		System.out.println(SqlServer2005PageHepler.getLimitString(sql, 1, 10));
		System.out.println(SqlCountHepler.getCountString(sql));
		
		sql = "select u.sex,count(1) c,max(u.id) t_max,min(u.name) as t_min from users u group by u.sex having count(1) > 10 order by count(u.sex)";
		System.out.println(SqlCountHepler.getCountString(sql));
		System.out.println(SqlCountHepler.getCountString(sql));
		

		sql = "select u.username,count(1) c,max(u.id) t_max from tplt_user u group by u.username having count(1) > 5 order by count(u.username) DESC";
		System.out.println(SqlServer2005PageHepler.getLimitString(sql,1, 10));
		System.out.println(SqlCountHepler.getCountString(sql));

执行结果：

select * from (select ROW_NUMBER() OVER (order by u.id  ) AS row_num_ ,  u.name from Users u ) t where row_num_ > 1 and row_num_ <=  10
select count (1) count_  from Users u 
select count(1) count_ from (select u.sex,count(1) c,max(u.id) t_max,min(u.name) as t_min from users u group by u.sex having count(1) > 10  ) t_
select count(1) count_ from (select u.sex,count(1) c,max(u.id) t_max,min(u.name) as t_min from users u group by u.sex having count(1) > 10  ) t_
select * from (select ROW_NUMBER() OVER (order by count(u.username) DESC ) AS row_num_ ,  u.username,count(1) c,max(u.id) t_max from tplt_user u group by u.username having count(1) > 5 ) t where row_num_ > 1 and row_num_ <=  10
select count(1) count_ from (select u.username,count(1) c,max(u.id) t_max from tplt_user u group by u.username having count(1) > 5  ) t_

不支持"UNION " 和“UNION ALL ” 这样的结果合并语句，例如：

select * from dbo.[user]
union
select top 1 * from dbo.[user] order by age

如果你需要得到正确的结果你需要这样写，如下：

select * from (
	select * from dbo.[user]
	union 
	select top 1 * from dbo.[user] order by age
) Q order by id