查询根据某一列去重，去updateDate最新的一条记录

您好，可以使用以下 SQL 语句实现您的需求：

SELECT DISTINCT t1.*
FROM your_table t1
LEFT JOIN your_table t2 
ON t1.column_name = t2.column_name 
AND t1.updateDate < t2.updateDate
WHERE t2.column_name IS NULL;

其中，`your_table` 是您要查询的表名，`column_name` 是您要去重的列名，`updateDate` 是您要比较的日期列名。

该 SQL 语句的思路是，先将所有记录按照 `column_name` 分组，然后在每组内按照 `updateDate` 从大到小排序，保留每组内的第一条记录，即为最新的一条记录。最后使用 `DISTINCT` 去重，得到每个 `column_name` 最新的一条记录。

相关问题

sql根据某一列去重，去updateDate最新的一条记录

可以使用以下SQL语句实现：

DELETE FROM your_table
WHERE id NOT IN (
  SELECT MAX(id) 
  FROM your_table 
  GROUP BY column_name 
)

其中，`your_table`是你要去重的表名，`column_name`是你要根据哪一列去重。这条SQL语句会删除`your_table`中除了每个`column_name`最新的记录外的所有记录。`id`是该表的主键或唯一标识列，用来确定记录的顺序。

需要注意的是，这条SQL语句会删除记录，所以请谨慎操作。建议在执行前先备份数据。

用java写具体代码 (1)基本信息管理 管理人员的基本信息，包括：增加员工信息、修改基础信息、删除员工信息、查询基础信息；维护部门信息，包括：增加、修改、删除、查询等操作。 (2)人员调动管理 管理人员的调动情况，记录人员的调动历史并提供查询功能。 (3)人员考核管理 管理人员的考核情况，记录人员的考核历史并提供查询功能。 (4)劳资管理 管理人员的劳资分配情况，记录人员的劳资更改历史并提供查询功能。数据库包括人员信息表(编号、姓名、性别、出生年月、民族、地址、部门、薪酬、考核、其他)，历史操作记录表(流水编号、操作类型、原始信息、更新信息、变更次数、变更日期、人员编号)，部门管理表(编号、一级部门、二级部门)。可以根据自己的设计在此基础上增加数据。

以下是一个基本的Java代码示例，实现了对员工信息和部门信息的增删改查，以及记录员工调动、考核和劳资分配的历史操作：

import java.util.ArrayList;
import java.util.HashMap;

public class EmployeeManagementSystem {
    //员工信息表
    private HashMap<Integer, Employee> employees;
    //历史操作记录表
    private ArrayList<OperationRecord> operationRecords;
    //部门管理表
    private HashMap<Integer, Department> departments;

    public EmployeeManagementSystem() {
        employees = new HashMap<>();
        operationRecords = new ArrayList<>();
        departments = new HashMap<>();
    }

    //增加员工信息
    public void addEmployee(Employee employee) {
        employees.put(employee.getId(), employee);
        //记录操作历史
        OperationRecord record = new OperationRecord(OperationType.ADD_EMPLOYEE, null, employee, 1, new Date(), employee.getId());
        operationRecords.add(record);
    }

    //修改基础信息
    public void updateEmployee(Employee employee) {
        Employee oldEmployee = employees.get(employee.getId());
        employees.put(employee.getId(), employee);
        //记录操作历史
        OperationRecord record = new OperationRecord(OperationType.UPDATE_EMPLOYEE, oldEmployee, employee, oldEmployee.getUpdateCount() + 1, new Date(), employee.getId());
        operationRecords.add(record);
    }

    //删除员工信息
    public void deleteEmployee(int employeeId) {
        Employee employee = employees.remove(employeeId);
        //记录操作历史
        OperationRecord record = new OperationRecord(OperationType.DELETE_EMPLOYEE, employee, null, 1, new Date(), employeeId);
        operationRecords.add(record);
    }

    //查询基础信息
    public Employee getEmployee(int employeeId) {
        return employees.get(employeeId);
    }

    //增加部门信息
    public void addDepartment(Department department) {
        departments.put(department.getId(), department);
    }

    //修改部门信息
    public void updateDepartment(Department department) {
        departments.put(department.getId(), department);
    }

    //删除部门信息
    public void deleteDepartment(int departmentId) {
        departments.remove(departmentId);
    }

    //查询部门信息
    public Department getDepartment(int departmentId) {
        return departments.get(departmentId);
    }

    //记录人员调动历史
    public void recordEmployeeTransfer(int employeeId, Department oldDepartment, Department newDepartment) {
        Employee employee = employees.get(employeeId);
        //记录操作历史
        OperationRecord record = new OperationRecord(OperationType.TRANSFER_EMPLOYEE, oldDepartment, newDepartment, employee.getUpdateCount() + 1, new Date(), employeeId);
        operationRecords.add(record);
    }

    //记录人员考核历史
    public void recordEmployeeAssessment(int employeeId, Assessment assessment) {
        Employee employee = employees.get(employeeId);
        //记录操作历史
        OperationRecord record = new OperationRecord(OperationType.ASSESS_EMPLOYEE, null, assessment, employee.getUpdateCount() + 1, new Date(), employeeId);
        operationRecords.add(record);
    }

    //记录劳资更改历史
    public void recordEmployeeSalaryChange(int employeeId, double oldSalary, double newSalary) {
        Employee employee = employees.get(employeeId);
        //记录操作历史
        OperationRecord record = new OperationRecord(OperationType.CHANGE_EMPLOYEE_SALARY, oldSalary, newSalary, employee.getUpdateCount() + 1, new Date(), employeeId);
        operationRecords.add(record);
    }
}

class Employee {
    private int id;
    private String name;
    private String gender;
    private Date birthDate;
    private String nation;
    private String address;
    private Department department;
    private double salary;
    private Assessment assessment;
    private int updateCount;
    private String other;

    public Employee(int id, String name, String gender, Date birthDate, String nation, String address, Department department, double salary, Assessment assessment, String other) {
        this.id = id;
        this.name = name;
        this.gender = gender;
        this.birthDate = birthDate;
        this.nation = nation;
        this.address = address;
        this.department = department;
        this.salary = salary;
        this.assessment = assessment;
        this.updateCount = 0;
        this.other = other;
    }

    //getter和setter方法省略

    public int getUpdateCount() {
        return updateCount;
    }
}

class Department {
    private int id;
    private String firstLevel;
    private String secondLevel;

    public Department(int id, String firstLevel, String secondLevel) {
        this.id = id;
        this.firstLevel = firstLevel;
        this.secondLevel = secondLevel;
    }

    //getter和setter方法省略
}

class OperationRecord {
    private OperationType operationType;
    private Object oldValue;
    private Object newValue;
    private int updateCount;
    private Date updateDate;
    private int employeeId;

    public OperationRecord(OperationType operationType, Object oldValue, Object newValue, int updateCount, Date updateDate, int employeeId) {
        this.operationType = operationType;
        this.oldValue = oldValue;
        this.newValue = newValue;
        this.updateCount = updateCount;
        this.updateDate = updateDate;
        this.employeeId = employeeId;
    }

    //getter和setter方法省略
}

class Assessment {
    private double score;
    private Date date;
    private String comment;

    public Assessment(double score, Date date, String comment) {
        this.score = score;
        this.date = date;
        this.comment = comment;
    }

    //getter和setter方法省略
}

enum OperationType {
    ADD_EMPLOYEE, UPDATE_EMPLOYEE, DELETE_EMPLOYEE, TRANSFER_EMPLOYEE, ASSESS_EMPLOYEE, CHANGE_EMPLOYEE_SALARY
}