mysql 清楚多列重复数据只保留其中一条 -清零世界

bubuko 2022/1/25 19:58:57 mysql 字数 4645 阅读 629 0 来源 http://www.bubuko.com/infolist-5-1.html

-- 创建临时表存储查询出的数据
drop temporary table if exists childtable;
create temporary table childtable (select * from tb_child a where 
(a.patriarch_id,a.student_id) in (select patriarch_id,student_id from tb_child group by patriarch_id,student_id 
having count(*) > 1)
and child_id not in (select min(child_id) from tb_child group 
by patriarch_id,student_id having count(*)>1));

select *From childtable
drop temporary table childtable;
-- 根据条件删除临时表格中的数据
delete from tb_child  where  child_id in (select childtable.child_id from childtable)

--查询出 绑定关系表中 家长id和学生id重复的数据。但是不包含 绑定关系id最小的那一条记录
select * from tb_child a where 
(a.patriarch_id,a.student_id) in (select patriarch_id,student_id from tb_child group by patriarch_id,student_id 
having count(*) > 1)
and child_id not in (select min(child_id) from tb_child group 
by patriarch_id,student_id having count(*)>1)

-- 查询出具有patriarch_id ，student_id 重复的数据（排除id最小的第一条数据） 带行号
select (@rownum := @rownum + 1) AS rownum ,a.* from tb_child a, (SELECT  @rownum := 0) AS rn  where 
(a.patriarch_id,a.student_id) in (select patriarch_id,student_id from tb_child group by patriarch_id,student_id 
having count(*) > 1)
and child_id not in (select min(child_id) from tb_child group 
by patriarch_id,student_id having count(*)>1)

--查询出具有patriarch_id ，student_id 重复的数据 带行号
select (@rownum := @rownum + 1) AS rownum ,ci.* from tb_child ci, (SELECT 
    @rownum := 0) AS rn  where patriarch_id in (select patriarch_id from tb_child group by patriarch_id,student_id having count(*) > 1)

mysql 清楚多列重复数据只保留其中一条

原文：https://www.cnblogs.com/chongyao/p/12667942.html

如果您也喜欢它,动动您的小指点个赞吧

mysql 清楚多列重复数据只保留其中一条

分类汇总

您的鼓励是对我最大的支持