ID NAME
1 chang
1 chang
2 zhang
2 zhang
有没有一句SQL 删除重复记录,保留任意一个
记住:一句话;最好能分组删除(name列相同的为同一组)。
mssql下的
1 chang
1 chang
2 zhang
2 zhang
有没有一句SQL 删除重复记录,保留任意一个
记住:一句话;最好能分组删除(name列相同的为同一组)。
mssql下的
解决方案 »
- asp.net mvc
- IIS内设置了一个网站,如何共存.net4.0和.net2.0的两个虚拟目录
- 请问用asp.net开发安卓手机上的软件该怎么做?
- 高分求datagrid操作的简单的问题,会者很易!
- M份试卷,评阅次数N,评阅教师人数为T,求怎么分每个教师才不会分到重复的试卷?给出最优的算法
- 求Host Integration Server详细配置文档
- 怎么在后发读取confirm()的值?
- DataGrid难题啊
- 今天面试,郁闷,有个问题没回答出来,进来看看
- 怎么使RequiredFieldValidator控件的报错为弹出一JavaScript警告窗口
- Sql语句
- 电子商务数据库的数据结构
delete table where ID not in (select max(ID) from table group by name)
DELETE A
from Tb A,(
SELECT NAME FROM Tb GROUP BY NAME HAVING COUNT(1)>1) as t
where t.NAME = A.NAME
select * from table where ID in (select max(ID) from table group by name)http://www.mybuffet.cn
(
ID int,
NAME varchar(20)
)
insert into #EE select 1,'chang'
insert into #EE select 1,'chang'
insert into #EE select 2,'zhang'
insert into #EE select 2,'zhang'
;with hgo as
(
select *,
row_number() over(partition by ID order by Name) rank from #EE
)
delete h from hgo h where ID=h.ID and rank=1 select * from #EEID NAME
----------- --------------------
1 chang
2 zhang
(
ID int,
NAME varchar(20)
)
insert into #EE select 1,'chang'
insert into #EE select 1,'chang'
insert into #EE select 2,'zhang'
insert into #EE select 2,'zhang'
--sql200
delete from
(
select *,(select count(*)+1 from #EE group by ID having(ID)>1) rank from #EE
) EE
where rank=2
--sql2005
;with hgo as
(
select *,
row_number() over(partition by ID order by Name) rank from #EE
)
delete h from hgo h where ID=h.ID and rank=1 select * from #EE
delete MemberInfo where ID not in (
select max(ID) from MemberInfo group by MemberName, MemberAddress)
要是不想加,就看我的博客,希望对你有所帮助。
http://blog.csdn.net/zuoming120/archive/2009/10/21/4709521.aspx
你需要一个存储过程解决比较方便
首先用聚合函数COUNT统计出每种相同的个数
我想你那样的应该是不规则情况 有1个的,3个的 11个的类似
所以你需要判断下有多少个 然后去除掉这个关键字的count(*)-1次
就是你需要达到的条件了