Druid重组SQL语句，实现动态group by

最新推荐文章于 2024-10-21 22:14:56 发布

熊與猫v

最新推荐文章于 2024-10-21 22:14:56 发布

阅读量1k

点赞数

分类专栏：随笔文章标签： sql 数据库 sqlserver

本文链接：https://blog.youkuaiyun.com/qq_36448800/article/details/121104477

版权

随笔专栏收录该内容

9 篇文章

订阅专栏

本文介绍了一种方法，用于动态地重构 SQL 查询中的 GROUP BY 子句。该方法首先解析 SQL 语句，然后根据提供的字段列表更新 GROUP BY 和 SELECT 列表，确保 GROUP BY 中的所有字段都出现在 SELECT 列表中。

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

  public static SQLSelect getSqlSelect(String sql, List<String> groupByList, String dbType) {
        if (CollectionUtils.isEmpty(groupByList)) {
            return null;
        }
    
        List<SQLStatement> statements = SQLUtils.parseStatements(sql, dbType);
        SQLSelectStatement selectStatement = (SQLSelectStatement) statements.get(0);
        SQLSelect select = selectStatement.getSelect();
        if (select.getQuery() instanceof SQLUnionQuery) {
            SQLUnionQuery sqlUnionQuery = (SQLUnionQuery) select.getQuery();
            SQLSelectQueryBlock rightQuery = (SQLSelectQueryBlock) sqlUnionQuery.getRight();
            rebuildQueryGroupByAndSelect(groupByList, rightQuery);
        
            SQLSelectQueryBlock leftQuery = (SQLSelectQueryBlock) sqlUnionQuery.getLeft();
            rebuildQueryGroupByAndSelect(groupByList, leftQuery);
        } else {
            SQLSelectQueryBlock query = (SQLSelectQueryBlock) select.getQuery();
            rebuildQueryGroupByAndSelect(groupByList, query);
        }
        return select;
    }

private static void rebuildQueryGroupByAndSelect(List<String> groupByList, SQLSelectQueryBlock query) {
        // 将 旧的group by的字段从select item 换成新的group by 字段
        SQLSelectGroupByClause oldGroupBy = query.getGroupBy();
        List<SQLSelectItem> selectList = query.getSelectList();
        if (oldGroupBy != null) {
            List<SQLExpr> oldGroupByItems = oldGroupBy.getItems();
           
            for (SQLExpr oldGroupByItem : oldGroupByItems) {
                int j = selectList.size();
                // 如果group by 是 a，则 a 和 t.a都可以删
                if (oldGroupByItem instanceof SQLIdentifierExpr) {
                    for (int i = j - 1; i >= 0; i--) {
                        SQLSelectItem selectItem = selectList.get(i);
                        if (selectItem.getExpr() instanceof SQLIdentifierExpr) {
                            if (oldGroupByItem.toString().equalsIgnoreCase(selectItem.getExpr().toString())) {
                                selectList.remove(i);
                            }
                        } else  if (selectItem.getExpr() instanceof SQLPropertyExpr) {
                            SQLPropertyExpr propertyExpr = (SQLPropertyExpr) selectItem.getExpr();
                            if (oldGroupByItem.toString().equalsIgnoreCase(propertyExpr.getName())) {
                                selectList.remove(i);
                            }
                        }
                    }
                } else if(oldGroupByItem instanceof SQLPropertyExpr) {
                    //如果 group by 是 t.a, 则 a 和 t.a都可以删
                    String name = ((SQLPropertyExpr) oldGroupByItem).getName();
                    String expr = oldGroupByItem.toString();
                    for (int i = j - 1; i >= 0; i--) {
                        SQLSelectItem selectItem = selectList.get(i);
                        if (selectItem.getExpr() instanceof SQLIdentifierExpr) {
                            if (name.equalsIgnoreCase(selectItem.getExpr().toString())) {
                                selectList.remove(i);
                            }
                        } else  if (selectItem.getExpr() instanceof SQLPropertyExpr) {
                            if (selectItem.getExpr().toString().equalsIgnoreCase(expr)) {
                                selectList.remove(i);
                            }
                        }
                    }
                }
            }
        }
        // 加上新的group by的字段到select
        for (String selectItem : groupByList) {
            selectList.add(new SQLSelectItem(new SQLIdentifierExpr(selectItem)));
        }
        
        // 重组group by
        SQLSelectGroupByClause group = new SQLSelectGroupByClause();
        for (String s : groupByList) {
            group.addItem(new SQLIdentifierExpr(s));
        }
        query.setGroupBy(group);
    }