Mysql中表student_table(id,name,birth,sex),删除name重复的id最大的记录,比如'张三'重复2次,id分别是1、2,则删除id=2的记录,保留id=1的记录。如下SQL正确的是()?
delete from student_table where id in (
select t2.*
from
(select name,count(*) as c1 from student_table GROUP BY name having c1 > 1)t1
left join
(select name, max(id) as id from student_table group by name ) t2
on t1.name = t2.name ) ;
delete from student_table t0
inner join (
select t2.*
from
(select name,count(*) as c1 from student_table GROUP BY name having c1 > 1)t1
left join
(select name, max(id) as id from student_table group by name ) t2
on t1.name = t2.name ) t3
on t0.id = t3.id ;
delete t0
from student_table t0
inner join (
select t2.*
from
(select name,count(*) as c1 from student_table GROUP BY name having c1 > 1)t1
left join
(select name, max(id) as id from student_table group by name ) t2
on t1.name = t2.name ) t3
on t0.id = t3.id ;
delete student_table
from student_table t0
inner join (
select t2.*
from
(select name,count(*) as c1 from student_table GROUP BY name having c1 > 1)t1
left join
(select name, max(id) as id from student_table group by name ) t2
on t1.name = t2.name ) t3
on t0.id = t3.id ;