通过SQL的select语句自动得到count语句和得到sql server 2005 的分页查询

 

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

 

 

 

评论

1 楼 webeasymail 2008-09-28  

谢谢你提出了这个问题！
本来想10.1过后在完成这个BUG，但是想想一个错误的东西放在上面，不是很舒服，就抽出了一点时间修改了一下~