mysql 删除某一字段重复数据只保留一条

因为要把school_code，student_id 设置为唯一索引，所以原来数据库中数据不能重复

1.先删除status=2(逻辑删除) 重复数据 （in() 要在用一个子查询）

delete from sys_student where student_id 
in(select * from (select student_id from sys_student
 GROUP BY student_id,school_code having count(*)>1) s)
and status=2 

2. 删除一个学校中学号重复  保留最大id的那一条 重复的(in() 要在用一个子查询)

delete from sys_student where student_id in(select * from
(select student_id from sys_student GROUP BY student_id,
school_code having count(*)>1) s)
and id not in(select * from (select max(id) from sys_student group by student_id,school_code having count(* )>1) a)

多表关联更新：

 update sys_account set status=2  where id in ( select * from(
 select sa.id from sys_account sa inner join sys_account_bind_identity i on(sa.id=i.account_id) 
  where i.identity=2 and sa.id not in (select * from (select account_id from sys_student) a)  and sa.account in 
   (select * from (select account from sys_account group by account having count(*)>1) s))ss)

取最新一条数据  用HAVING 写法

SELECT *  FROM   ( SELECT * FROM student_photo_audit WHERE school_code = 'ORG000199'               
   HAVING 1 ORDER BY create_date DESC ) b GROUP BY  b.student_code