根据需求的高级筛选功能，我们需要支持条件组的嵌套和复杂的逻辑关系，让我们对现有的表结构进行优化：
-- 筛选条件组表
CREATE TABLE filter_condition_groups (
group_id INT PRIMARY KEY AUTO_INCREMENT,
scheme_id INT NOT NULL,
parent_group_id INT,
group_logic_operator ENUM('AND', 'OR') DEFAULT 'AND',
group_order INT DEFAULT 0,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (scheme_id) REFERENCES filter_schemes(scheme_id),
FOREIGN KEY (parent_group_id) REFERENCES filter_condition_groups(group_id)
);
-- 修改筛选条件表，增加条件组关联
ALTER TABLE filter_conditions
ADD COLUMN group_id INT NOT NULL AFTER scheme_id,
ADD FOREIGN KEY (group_id) REFERENCES filter_condition_groups(group_id);
-- 操作符定义表
CREATE TABLE filter_operators (
operator_id INT PRIMARY KEY AUTO_INCREMENT,
operator_code VARCHAR(50) NOT NULL UNIQUE,
operator_name VARCHAR(50) NOT NULL,
operator_type VARCHAR(20) NOT NULL,
supported_field_types VARCHAR(255) COMMENT '支持的字段类型，JSON数组',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
数据初始化
-- 初始化操作符数据
INSERT INTO filter_operators
(operator_code, operator_name, operator_type, supported_field_types)
VALUES
('equals', '等于', 'compare', '["text","number","date","select"]'),
('not_equals', '不等于', 'compare', '["text","number","date","select"]'),
('contains', '包含', 'text', '["text"]'),
('not_contains', '不包含', 'text', '["text"]'),
('greater_than', '大于', 'compare', '["number","date"]'),
('less_than', '小于', 'compare', '["number","date"]'),
('between', '介于', 'range', '["number","date"]'),
('in', '属于', 'multiple', '["select"]'),
('not_in', '不属于', 'multiple', '["select"]'),
('is_empty', '为空', 'null', '["text","number","date","select"]'),
('is_not_empty', '不为空', 'null', '["text","number","date","select"]');
业务实现
public class FilterScheme {
private Long schemeId;
private String schemeName;
private List groups;
}
public class FilterGroup {
private Long groupId;
private String logicOperator; // AND/OR
private List conditions;
private List subGroups;
}
public class FilterCondition {
private Long fieldId;
private String operator;
private String value;
}
保存筛选方案筛选
-- 1. 创建筛选方案
INSERT INTO filter_schemes
(employee_id, module_id, scheme_name)
VALUES (1, 1, '重要商机筛选');
-- 2. 创建根条件组
INSERT INTO filter_condition_groups
(scheme_id, group_logic_operator)
VALUES (1, 'AND');
-- 3. 创建子条件组
INSERT INTO filter_condition_groups
(scheme_id, parent_group_id, group_logic_operator)
VALUES (1, 1, 'OR');
-- 4. 添加具体筛选条件
INSERT INTO filter_conditions
(group_id, field_id, operator, filter_value)
VALUES
(2, 1, 'contains', '商机名称'),
(2, 2, 'equals', 'A级');
查询条件构建实例
public class FilterBuilder {
public String buildFilterSql(Long schemeId) {
StringBuilder sql = new StringBuilder();
FilterScheme scheme = getSchemeById(schemeId);
sql.append("SELECT * FROM module_data WHERE 1=1 ");
for (FilterGroup group : scheme.getGroups()) {
sql.append(buildGroupCondition(group));
}
return sql.toString();
}
private String buildGroupCondition(FilterGroup group) {
StringBuilder condition = new StringBuilder(" AND (");
// 处理条件
for (FilterCondition filter : group.getConditions()) {
condition.append(buildSingleCondition(filter))
.append(" ")
.append(group.getLogicOperator())
.append(" ");
}
// 处理子组
for (FilterGroup subGroup : group.getSubGroups()) {
condition.append(buildGroupCondition(subGroup))
.append(" ")
.append(group.getLogicOperator())
.append(" ");
}
// 移除最后一个逻辑运算符
condition.setLength(condition.length() - 4);
condition.append(")");
return condition.toString();
}
}
前端数据结构实例
interface FilterScheme {
schemeId: number;
schemeName: string;
groups: FilterGroup[];
}
interface FilterGroup {
groupId: number;
logicOperator: 'AND' | 'OR';
conditions: FilterCondition[];
subGroups: FilterGroup[];
}
interface FilterCondition {
fieldId: number;
fieldName: string;
operator: string;
value: any;
}
使用实例
-- 根据截图中的筛选条件示例
SELECT * FROM business_opportunities
WHERE 1=1
AND (
business_name = '测试商机'
OR (
customer_name LIKE '%测试客户%'
AND create_time BETWEEN '2022-01-01' AND '2022-12-31'
)
);
设计要点
- 条件组嵌套
- 支持无限层级的条件组嵌套
- 每个条件组可以包含多个条件和子条件组
- 使用parent_group_id实现层级关系
- 操作符管理
- 统一管理所有支持的操作符
- 根据字段类型限制可用的操作符
- 支持不同类型的值处理
- 值处理
- 支持单值、多值、范围值等多种形式
- 使用JSON格式存储复杂的值结构
- 提供值格式化和验证
- 性能优化
- 合理设计索引
- 缓存常用筛选方案
- 优化SQL生成策略
这个设计可以支持像截图中显示的复杂筛选条件，包括：
- 多个条件组合
- 不同的比较操作符
- 条件组的嵌套
- 灵活的AND/OR逻辑组合
