使用LambdaWrapper实现去重查询方式

select() 方法结合 distinct() 或 SQL 函数来实现。以下是几种常见的去重查询方式：

1. 使用 DISTINCT 关键字

方式一：单字段去重

// 查询所有不重复的 name
LambdaQueryWrapper<User> wrapper = new LambdaQueryWrapper<>();
wrapper.select(User::getName)
       .distinct();  // 添加 DISTINCT 关键字
List<User> users = userMapper.selectList(wrapper);

方式二：多字段去重

// 查询 name 和 age 组合不重复的记录
LambdaQueryWrapper<User> wrapper = new LambdaQueryWrapper<>();
wrapper.select(User::getName, User::getAge)
       .distinct();
List<User> users = userMapper.selectList(wrapper);

2. 使用 GROUP BY 去重

// 按 name 分组，查询每个不重复的 name
LambdaQueryWrapper<User> wrapper = new LambdaQueryWrapper<>();
wrapper.select(User::getName)
       .groupBy(User::getName);
List<User> users = userMapper.selectList(wrapper);

// 获取分组计数
wrapper.select(User::getName, "COUNT(*) as count")
       .groupBy(User::getName);

3. 复杂去重查询

示例一：去重并排序

LambdaQueryWrapper<User> wrapper = new LambdaQueryWrapper<>();
wrapper.select(User::getName)
       .distinct()
       .orderByAsc(User::getName);  // 按名称升序排列

List<User> users = userMapper.selectList(wrapper);

示例二：带条件的去重查询

LambdaQueryWrapper<User> wrapper = new LambdaQueryWrapper<>();
wrapper.select(User::getDepartmentId, User::getPosition)
       .distinct()
       .eq(User::getStatus, 1)  // 状态为激活的用户
       .ge(User::getCreateTime, "2023-01-01");  // 2023年以后创建

List<User> users = userMapper.selectList(wrapper);

4. 使用自定义 SELECT 语句去重

// 使用 SQL 函数去重并统计
LambdaQueryWrapper<User> wrapper = new LambdaQueryWrapper<>();
wrapper.selectSql("DISTINCT department_id, COUNT(*) as user_count")
       .groupBy(User::getDepartmentId);

List<Map<String, Object>> result = userMapper.selectMaps(wrapper);

5. 获取去重后的单个字段值列表

// 获取所有不重复的部门ID列表
LambdaQueryWrapper<User> wrapper = new LambdaQueryWrapper<>();
wrapper.select(User::getDepartmentId)
       .distinct();

List<Long> departmentIds = userMapper.selectObjs(wrapper)
    .stream()
    .map(obj -> (Long) obj)
    .collect(Collectors.toList());

6. 完整示例

@Service
public class UserService {

    @Autowired
    private UserMapper userMapper;

    /**
     * 查询每个部门的不同职位
     */
    public List<Map<String, Object>> getDistinctPositionsByDepartment() {
        LambdaQueryWrapper<User> wrapper = new LambdaQueryWrapper<>();
        wrapper.select(User::getDepartmentId, User::getPosition)
               .distinct()
               .isNotNull(User::getDepartmentId)
               .isNotNull(User::getPosition)
               .orderByAsc(User::getDepartmentId, User::getPosition);

        return userMapper.selectMaps(wrapper);
    }

    /**
     * 统计每个不重复姓名的用户数量
     */
    public List<Map<String, Object>> countUsersByDistinctName() {
        LambdaQueryWrapper<User> wrapper = new LambdaQueryWrapper<>();
        wrapper.select("DISTINCT name, COUNT(*) as user_count")
               .groupBy(User::getName)
               .having("COUNT(*) > 1");  // 出现次数大于1的

        return userMapper.selectMaps(wrapper);
    }

    /**
     * 分页查询去重结果
     */
    public Page<User> getDistinctUsersByPage(int page, int size) {
        Page<User> pageParam = new Page<>(page, size);
        LambdaQueryWrapper<User> wrapper = new LambdaQueryWrapper<>();
        wrapper.select(User::getName, User::getEmail)
               .distinct()
               .orderByDesc(User::getCreateTime);

        return userMapper.selectPage(pageParam, wrapper);
    }
}

注意事项

实体类示例

@Data
@TableName("user")
public class User {
    private Long id;
    private String name;
    private Integer age;
    private String email;
    private Long departmentId;
    private String position;
    private Integer status;
    private LocalDateTime createTime;
}

这些方法涵盖了大多数去重查询场景，可以根据具体需求选择合适的方式。