请问以下两个功能如何实现?
(1)如何选出重复的记录?
ID cname tc bm
-------------------------
1 张三 1
2 李四 1
3 张三 2 行政
4 王五 1
5 李四 2 销售
6 李六 1
7 张八 2 设计
(2)筛选重复的记录,把重复记录的tc为2的bm字段的内容更新到tc为1的相应的姓名中,得到的结果为:
ID cname tc bm
-------------------------
1 张三 1 行政
2 李四 1 销售
4 王五 1
6 李六 1
7 张八 2 设计
-------------------------------------------------------------------------------------------------------------------
1、select cname ,tc ,bm from tabname group by cname ,tc ,bm having count(*) > 1
2、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=1
update 表 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=1
3.删除重复记录
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
----------------------------------------------------------------------------------------------------------------------------
自己在用的,SQLServer 与 Oracle通用:
查找所有重复记录:
select * from customer c inner join (
select acc,no from customer where active=1 group by acc,no having count(*) > 1
)t on c.acc=t.acc and c.no=t.no
更新所有重复记录:
update customer set active = 0 where id in(
select id from customer c inner join (
select acc,no from customer where active=1 group by acc,no having count(*) > 1
)t on c.acc=t.acc and c.no=t.no
)