mysql 删除重复数据 关联自己 关联子查询 delete
有手工录入的数据时，删除系统定时任务计算的数据。
delete from t1 using data_tab as t1, (select * from (select input_type,system_code,DATE_FORMAT(start_time,'%Y-%m-%d') as date_ from data_tab where start_time >='2024-05-20' and start_time <'2024-05-27'group by system_code,DATE_FORMAT(start_time,'%Y-%m-%d'),input_type)t group by system_code,date_ HAVING count(1)>1) as t2
where t1.system_code = t2.system_code and DATE_FORMAT(t1.start_time,'%Y-%m-%d') = t2.date_
and t1.start_time >='2024-05-20' and t1.start_time <'2024-05-27'
and t1.input_type='sys'
sql分析，delete from 后面跟着的，是需要删除数据的表。关联的表是查询出来的结果表。
注意：牢记！！！一定要给删除做条件限制，例如：时间，状态，类别，等等。这些常规的，在做删除操作的时候，要作为惯性。不然会删除大量数据。
当然，在删除之前，一定要用select语句检验一下，看看条件是否正确。
select * from data_tab t1
left join (select * from (select input_type,system_code,DATE_FORMAT(start_time,'%Y-%m-%d') as date_ from data_tab where start_time >='2024-05-01' and start_time <'2024-05-08'group by system_code,DATE_FORMAT(start_time,'%Y-%m-%d'),input_type)t group by system_code,date_ HAVING count(1)>1) as t2 on t1.system_code = t2.system_code and DATE_FORMAT(t1.start_time,'%Y-%m-%d') = t2.date_
where t1.start_time >='2024-05-01' and t1.start_time <'2024-05-08'
and t1.input_type='sys'
and t1.system_code = '1339849364182729817'