sqlserver子查询分页优化_sqlserver 子查询嵌套查询分页慢卡-优快云博客

智能将SqlServer的查询语句转换为分页语句

原创 2015年01月27日 13:17:21

主要用到了jsqlparser，前面有篇博客介绍过：

JAVA - Sql解析工具jsqlparser简单使用

为了给Mybatis分页插件增加对sqlserver的支持，专门写了这样一个独立的工具，只依赖jsqlparser。

这个类不仅是为了给分页插件使用的，他还能独立使用，使用它你可以方便的生成一个分页查询。

分页插件地址：Mybatis_PageHelper

SqlServer分页转换完整代码：com/github/pagehelper/SqlServer.java

简单讲一下处理的逻辑：

通过对SqlServer进行分析，利用jsqlparser方便的解析，然后对sql结构进行修改，生成最后的分页语句。

首先一个sql通常有两种情况，一种是普通的一个select查询，一种是通过union,minus等连接的多个查询。

当发现是多个查询的时候，会在原来的SQL基础上在外面包含一层查询，让原来的查询变成子查询。

外层的查询会从多个查询中的第一个查询中提取查询列（有别名的使用别名），因为每个查询的列都是一样的，所以找一个提取就行。

另外在多个SQL中的最后一个相比其他来说可能会多一些条件，这里主要考虑的是order by，如果有order by语句，会把order by移到外层SQL上。

做完上面的处理后，就和第一种普通的一个select查询一样了。

接下来处理这一个select查询。

第一步先获取查询列，并且会对别名和表名进行一些特殊处理。

第二步给SQL增加ROW_NUMBER()，将order by提取到OVER中

第三步处理全部子查询，如果子查询包含order by，会增加top 100 percent

第四步在select查询外包一层TOP查询。

经过上面的步骤就能得到一个合理结构的分页查询了。

其中有一些细节性的东西jsqlparser都考虑到了，不需要自己去特殊处理，例如distinct。

下面是两个例子。

这个类是独立的，使用的时候可以初始化一个，然后直接调用方法即可。

初始化：

[java]view plain copy 
    
 public static final SqlServer sqlServer = new SqlServer();  

第一个，多个查询UNION ALL

[java]view plain copy 
    
 @Test  
 public void testSqlUnion() throws JSQLParserException {  
     String originalSql = "select countryname,countrycode code from country where id >170 " +  
             "union all " +  
             "select countryname,countrycode code from country where id < 10 order by code";  
     System.out.println(sqlServer.convertToPageSql(originalSql, 1, 10));  
 }  

生成的SQL如下（经过人工格式化）：

[sql]view plain copy 
    
 SELECT TOP 10 PAGE_TABLE_ALIAS.countryname, PAGE_TABLE_ALIAS.code  
   FROM (SELECT ROW_NUMBER() OVER(ORDER BY code) PAGE_ROW_NUMBER,  
                WRAP_OUTER_TABLE.countryname,  
                WRAP_OUTER_TABLE.code  
           FROM ((SELECT countryname, countrycode code  
                    FROM country  
                   WHERE id > 170) UNION ALL  
                 (SELECT countryname, countrycode code  
                    FROM country  
                   WHERE id < 10)) AS WRAP_OUTER_TABLE) AS PAGE_TABLE_ALIAS  
  WHERE PAGE_ROW_NUMBER > 1  
  ORDER BY PAGE_ROW_NUMBER  

第二个，简单查询

[java]view plain copy 
    
 @Test  
 public void testSqlDistinct() throws JSQLParserException {  
     String originalSql = "select distinct countrycode,countryname from country order by countrycode";  
     System.out.println(sqlServer.convertToPageSql(originalSql, 1, 10));  
 }  

生成的SQL如下（经过人工格式化）：

[sql]view plain copy 
    
 SELECT TOP 10 PAGE_TABLE_ALIAS.countrycode, PAGE_TABLE_ALIAS.countryname  
   FROM (SELECT DISTINCT ROW_NUMBER() OVER(ORDER BY countrycode) PAGE_ROW_NUMBER,  
                         countrycode,  
                         countryname  
           FROM country) AS PAGE_TABLE_ALIAS  
  WHERE PAGE_ROW_NUMBER > 1  
  ORDER BY PAGE_ROW_NUMBER  

注意：

1.由于需要提取order by，所以尽可能保证最外层的SQL包含order by

2.如果没有order by，那么上面调用的convertToPageSql还有第四个参数orderBy

[java]view plain copy 
    
 public String convertToPageSql(String sql, int offset, int limit, String orderBy)   

如果原来的sql有order by，那么通过该方法指定orderBy之后会覆盖原sql中的order by

人为指定的时候很难把握字段名字的写法，所以建议在sql中带上order by