SQLServer去除子查询中的order by

港控/mmm° 2022-09-30 09:52 196阅读 0赞

SQL解析工具：SqlParser ，GitHub地址 [https://github.com/JSQLParser/JSqlParser][https_github.com_JSQLParser_JSqlParser]

本次演示版本：[http://search.maven.org/remotecontent?filepath=com/github/jsqlparser/jsqlparser/0.9.1/jsqlparser-0.9.1.jar][http_search.maven.org_remotecontent_filepath_com_github_jsqlparser_jsqlparser_0.9.1_jsqlparser-0.9.1.jar]

转换工具类（先导入下载的jar包）：SqlServerParse.java

package com.netide.plugin;
    
    import java.util.List;
    
    import net.sf.jsqlparser.JSQLParserException;
    import net.sf.jsqlparser.parser.CCJSqlParserUtil;
    import net.sf.jsqlparser.statement.Statement;
    import net.sf.jsqlparser.statement.select.FromItem;
    import net.sf.jsqlparser.statement.select.Join;
    import net.sf.jsqlparser.statement.select.LateralSubSelect;
    import net.sf.jsqlparser.statement.select.OrderByElement;
    import net.sf.jsqlparser.statement.select.PlainSelect;
    import net.sf.jsqlparser.statement.select.Select;
    import net.sf.jsqlparser.statement.select.SelectBody;
    import net.sf.jsqlparser.statement.select.SetOperationList;
    import net.sf.jsqlparser.statement.select.SubJoin;
    import net.sf.jsqlparser.statement.select.SubSelect;
    import net.sf.jsqlparser.statement.select.ValuesList;
    import net.sf.jsqlparser.statement.select.WithItem;
    
    public class SqlServerParse {
    	
    	public String removeOrderBy(String sql) throws JSQLParserException {
    		Statement stmt = CCJSqlParserUtil.parse(sql);
    		Select select = (Select) stmt;
    		SelectBody selectBody = select.getSelectBody();
    		processSelectBody(selectBody);
    		return select.toString();
    	}
    
    	public void processSelectBody(SelectBody selectBody) {
    		if (selectBody instanceof PlainSelect) {
    			processPlainSelect((PlainSelect) selectBody);
    		} else if (selectBody instanceof WithItem) {
    			WithItem withItem = (WithItem) selectBody;
    			if (withItem.getSelectBody() != null) {
    				processSelectBody(withItem.getSelectBody());
    			}
    		} else {
    			SetOperationList operationList = (SetOperationList) selectBody;
    			if (operationList.getPlainSelects() != null && operationList.getPlainSelects().size() > 0) {
    				List<PlainSelect> plainSelects = operationList.getPlainSelects();
    				for (PlainSelect plainSelect : plainSelects) {
    					processPlainSelect(plainSelect);
    				}
    			}
    			if (!orderByHashParameters(operationList.getOrderByElements())) {
    				operationList.setOrderByElements(null);
    			}
    		}
    	}
    
    	public void processPlainSelect(PlainSelect plainSelect) {
    		if (!orderByHashParameters(plainSelect.getOrderByElements())) {
    			plainSelect.setOrderByElements(null);
    		}
    		if (plainSelect.getFromItem() != null) {
    			processFromItem(plainSelect.getFromItem());
    		}
    		if (plainSelect.getJoins() != null && plainSelect.getJoins().size() > 0) {
    			List<Join> joins = plainSelect.getJoins();
    			for (Join join : joins) {
    				if (join.getRightItem() != null) {
    					processFromItem(join.getRightItem());
    				}
    			}
    		}
    	}
    
    	public void processFromItem(FromItem fromItem) {
    		if (fromItem instanceof SubJoin) {
    			SubJoin subJoin = (SubJoin) fromItem;
    			if (subJoin.getJoin() != null) {
    				if (subJoin.getJoin().getRightItem() != null) {
    					processFromItem(subJoin.getJoin().getRightItem());
    				}
    			}
    			if (subJoin.getLeft() != null) {
    				processFromItem(subJoin.getLeft());
    			}
    		} else if (fromItem instanceof SubSelect) {
    			SubSelect subSelect = (SubSelect) fromItem;
    			if (subSelect.getSelectBody() != null) {
    				processSelectBody(subSelect.getSelectBody());
    			}
    		} else if (fromItem instanceof ValuesList) {
    
    		} else if (fromItem instanceof LateralSubSelect) {
    			LateralSubSelect lateralSubSelect = (LateralSubSelect) fromItem;
    			if (lateralSubSelect.getSubSelect() != null) {
    				SubSelect subSelect = (SubSelect) (lateralSubSelect.getSubSelect());
    				if (subSelect.getSelectBody() != null) {
    					processSelectBody(subSelect.getSelectBody());
    				}
    			}
    		}
    	}
    
    	public boolean orderByHashParameters(List<OrderByElement> orderByElements) {
    		if (orderByElements == null) {
    			return false;
    		}
    		for (OrderByElement orderByElement : orderByElements) {
    			if (orderByElement.toString().toUpperCase().contains("?")) {
    				return true;
    			}
    		}
    		return false;
    	}
    }

调用很简单，一句话搞定：

String newSql = SqlServerParse.class.newInstance().removeOrderBy(oldSql); //去除ORDER BY

[https_github.com_JSQLParser_JSqlParser]: https://github.com/JSQLParser/JSqlParser
[http_search.maven.org_remotecontent_filepath_com_github_jsqlparser_jsqlparser_0.9.1_jsqlparser-0.9.1.jar]: http://search.maven.org/remotecontent?filepath=com/github/jsqlparser/jsqlparser/0.9.1/jsqlparser-0.9.1.jar