1、select cname ,tc ,bm from tabname group by cname ,tc ,bm having count(*) > 12、select min(ID) as ID ,cname ,tc ,bm from tabname group by cname ,tc ,bm
什么样的记录算是重复的呢? 猜测是姓名相同的就算重复吧. 1. select 表.* from 表 join (select cname,count(cname) as num from 表 group by cname having count(cname)>1) a on 表.cname=a.cname -------------------------------5 李四 2 销售 2 李四 1 1 张三 1 3 张三 2 行政
update 表 set bm=a.bm from ( select 表.* from 表 join (select cname,count(cname) as num from 表 group by cname having count(cname)>1) a on 表.cname=a.cname ) a where 表.cname=a.cname and 表.tc=1update 表 set bm='' from ( select 表.* from 表 join (select cname,count(cname) as num from 表 group by cname having count(cname)>1) a on 表.cname=a.cname ) a where 表.cname=a.cname and 表.tc=2
1.找出重复记录 select * from 表 a where exists (select 1 from 表 where cname=a.cname group by cname having count(cname)>1)2.替换 update 表 set bm=c.bm from (select b.* from 表 b join (select cname,max(tc) as tc from 表 a where exists (select 1 from 表 where cname=a.cname group by cname having count(cname)>1) group by cname ) a on b.cname=a.cname and b.tc=a.tc ) c where 表.cname=c.cname and 表.tc=13.删除重复记录 delete from 表 where id in ( select a.id from 表 a join ( select cname,min(tc) as tc from 表 a where exists (select 1 from 表 where cname=a.cname group by cname having count(cname)>1) group by cname ) b on a.cname=b.cname and a.tc=b.tc )
哦,第三步不对,不是删除重复,而是 update 表 set bm='' from (select b.* from 表 b join (select cname,min(tc) as tc from 表 a where exists (select 1 from 表 where cname=a.cname group by cname having count(cname)>1) group by cname ) a on b.cname=a.cname and b.tc=a.tc ) c where 表.cname=c.cname and 表.tc=2
1.
select 表.* from 表 join (select cname,count(cname) as num from 表 group by cname having count(cname)>1) a
on 表.cname=a.cname
-------------------------------5 李四 2 销售
2 李四 1
1 张三 1
3 张三 2 行政
(
select 表.* from 表 join (select cname,count(cname) as num from 表 group by cname having count(cname)>1) a
on 表.cname=a.cname
) a where 表.cname=a.cname and 表.tc=1update 表 set bm='' from
(
select 表.* from 表 join (select cname,count(cname) as num from 表 group by cname having count(cname)>1) a
on 表.cname=a.cname
) a where 表.cname=a.cname and 表.tc=2
select * from 表 a where exists (select 1 from 表 where cname=a.cname group by cname having count(cname)>1)2.替换
update 表 set bm=c.bm from
(select b.* from 表 b join
(select cname,max(tc) as tc from 表 a where exists (select 1 from 表 where cname=a.cname group by cname having count(cname)>1)
group by cname
) a on b.cname=a.cname and b.tc=a.tc
) c where 表.cname=c.cname and 表.tc=13.删除重复记录
delete from 表 where id in
(
select a.id from 表 a join
(
select cname,min(tc) as tc from 表 a where exists (select 1 from 表 where cname=a.cname group by cname having count(cname)>1)
group by cname
) b
on a.cname=b.cname and a.tc=b.tc
)
update 表 set bm='' from
(select b.* from 表 b join
(select cname,min(tc) as tc from 表 a where exists (select 1 from 表 where cname=a.cname group by cname having count(cname)>1)
group by cname
) a on b.cname=a.cname and b.tc=a.tc
) c where 表.cname=c.cname and 表.tc=2