在业务系统中，报表模块经常需要根据用户选择的分组维度、排序规则展示不同维度的分组排名数据，比如按部门、按区域、按产品类型分组，再根据销售额、完成率等指标进行排名，这类需求无法用固定的SQL语句实现，需要结合前端传入参数与窗口函数动态生成查询逻辑。
核心实现思路
整个实现流程分为三个部分：前端参数传递、后端参数处理与动态SQL生成、数据库执行窗口函数查询。前端将用户选择的分组字段、排序字段、排序方向、排名类型传递给后端，后端先对参数做合法性校验，再动态拼接SQL语句，最后执行查询返回结果。
前端参数设计
前端需要传递以下四类核心参数，所有参数都使用字符串形式传递，方便后端解析：
- group_field：分组字段名称，比如dept_name、region_name
- order_field：排序字段名称，比如sales_amount、completion_rate
- order_dir：排序方向，取值为ASC或者DESC
- rank_type：排名类型，取值为row_number、rank、dense_rank，分别对应三种常见的窗口排名函数
前端请求示例参数如下：
{
"group_field": "dept_name",
"order_field": "sales_amount",
"order_dir": "DESC",
"rank_type": "dense_rank"
}
后端参数校验与SQL拼接
后端收到参数后首先要做校验，避免非法字段名导致SQL错误或者注入风险，校验规则包括：分组字段、排序字段必须是数据库表中存在的字段，排序方向只能是ASC或者DESC，排名类型只能是三种窗口函数的名称。
校验通过后，动态拼接SQL的核心逻辑如下，以Java为例：
import java.util.Arrays;
import java.util.List;
public class DynamicRankSqlBuilder {
// 允许使用的字段白名单，避免SQL注入
private static final List<String> ALLOW_FIELDS = Arrays.asList("dept_name", "region_name", "product_type", "sales_amount", "completion_rate");
// 允许的排名类型白名单
private static final List<String> ALLOW_RANK_TYPES = Arrays.asList("row_number", "rank", "dense_rank");
public static String buildRankSql(String groupField, String orderField, String orderDir, String rankType) {
// 参数校验
if (!ALLOW_FIELDS.contains(groupField) || !ALLOW_FIELDS.contains(orderField)) {
throw new IllegalArgumentException("非法字段名");
}
if (!"ASC".equalsIgnoreCase(orderDir) && !"DESC".equalsIgnoreCase(orderDir)) {
throw new IllegalArgumentException("排序方向只能为ASC或DESC");
}
if (!ALLOW_RANK_TYPES.contains(rankType.toLowerCase())) {
throw new IllegalArgumentException("不支持的排名类型");
}
// 拼接SQL，使用占位符避免直接拼接值，这里字段名因为是白名单校验过的可以直接拼接
String sql = String.format(
"SELECT %s, %s, %s() OVER (PARTITION BY %s ORDER BY %s %s) AS rank_num FROM sales_table",
groupField, orderField, rankType.toLowerCase(), groupField, orderField, orderDir
);
return sql;
}
}
窗口函数说明
三种排名函数的区别如下：
| 函数名称 | 排名规则 | 示例结果（分数：100,90,90,80） |
|---|---|---|
| ROW_NUMBER | 连续不重复排名，相同值按顺序给不同序号 | 1,2,3,4 |
| RANK | 相同值排名相同，后续排名跳过重复数量 | 1,2,2,4 |
| DENSE_RANK | 相同值排名相同，后续排名连续不跳过 | 1,2,2,3 |
完整查询示例
假设前端传入参数为分组字段dept_name，排序字段sales_amount，排序方向DESC，排名类型dense_rank，拼接后的SQL如下：
SELECT dept_name, sales_amount, dense_rank() OVER (PARTITION BY dept_name ORDER BY sales_amount DESC) AS rank_num FROM sales_table
执行该SQL后，会先按dept_name分组，每个部门内部按sales_amount降序排名，相同销售额的排名一致，后续排名连续。
注意事项
- 所有动态拼接的字段名必须经过白名单校验，不能直接拼接前端传入的原始字符串，防止SQL注入攻击
- 如果排序字段是字符串类型，需要注意数据库的排序规则，避免中文排序出现不符合预期的结果
- 如果数据量较大，建议对分组字段和排序字段建立联合索引，提升窗口函数的执行效率
- 前端如果需要分页展示排名结果，可以在动态SQL外层再套一层查询，使用LIMIT和OFFSET实现分页
动态分组排名的核心是窗口函数的PARTITION BY子句动态指定分组字段，ORDER BY子句动态指定排序规则，结合前端的参数传递和后端的安全校验，就能满足大部分灵活的分组排名需求。