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

neversoft

　　

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