我companymap里 有 companyid 与chainid 两个字段
由于旧数据的原因,出现一个companyid 对应多个chainid
也就是像这样的数据 companyid=1,有2条以上的数据,
请问我怎样只留下一条数据呢
companyid chainid
1 1
1 2
1 3我现在只留
companyid chainid
1 1这样应该怎样做。
由于旧数据的原因,出现一个companyid 对应多个chainid
也就是像这样的数据 companyid=1,有2条以上的数据,
请问我怎样只留下一条数据呢
companyid chainid
1 1
1 2
1 3我现在只留
companyid chainid
1 1这样应该怎样做。
解决方案 »
- 在多个表中查询相同字段后得到的记录集,如何在里面进行进一步的筛选?
- 请问一个SQL语句
- 主键设置为递增了,在新插入数据时,能否将指定的值插入主键字段??急~~~~~~~~~~~~
- mssql2000 like '%欧美%' 可查到纪录 like '%娜%' 查不到 “欧美凯娜”
- 关于两个服务器上的 数据库 之间 存储过程的同步问题
- 不知道是架构还是前缀,2000导入2005数据出错
- 谁能帮忙把语句优化下????
- 提一个很简单的存储过程问题,急用
- 请问有没有字苻串的聚合函数??
- 如何实现将写入一个数据库中的数据以最快的速度备份到另一个数据库中(Sql语句或存储过程)
- 定时自动备份SQLServer数据库?
- SQL排程?
where exists(select 1 from companymap t where t.companyid=companymap.companyid and t.chainid<companymap.chainid)
insert into companymap values(1 , 1 )
insert into companymap values(1 , 2 )
insert into companymap values(1 , 3 )
insert into companymap values(2 , 11 )
insert into companymap values(2 , 22 )
insert into companymap values(2 , 32 )
go--删除前的数据
select * from companymap
/*
companyid chainid
----------- -----------
1 1
1 2
1 3
2 11
2 22
2 32(所影响的行数为 6 行)
*/--删除语句
delete companymap from companymap t where chainid not in (select min(chainid ) from companymap where companyid = t.companyid)--删除后的数据
select * from companymap
/*
companyid chainid
----------- -----------
1 1
2 11(所影响的行数为 2 行)
*/
drop table companymap
-- Author :fredrickhu(小F,向高手学习)
-- Date :2009-12-23 22:00:58
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([companyid] int,[chainid] int)
insert [tb]
select 1,1 union all
select 1,2 union all
select 1,3
--------------开始查询--------------------------
delete
t
from
tb t
where
chainid not in (select min(chainid ) from tb where companyid = t.companyid)
select * from tb
----------------结果----------------------------
/* companyid chainid
----------- -----------
1 1(1 行受影响)
*/
2、删除重复记录有大小关系时,保留大或小其中一个记录
--> --> (Roy)生成測試數據if not object_id('Tempdb..#T') is null
drop table #T
Go
Create table #T([ID] int,[Name] nvarchar(1),[Memo] nvarchar(2))
Insert #T
select 1,N'A',N'A1' union all
select 2,N'A',N'A2' union all
select 3,N'A',N'A3' union all
select 4,N'B',N'B1' union all
select 5,N'B',N'B2'
Go--I、Name相同ID最小的记录(推荐用1,2,3),保留最小一条
方法1:
delete a from #T a where exists(select 1 from #T where Name=a.Name and ID<a.ID)方法2:
delete a from #T a left join (select min(ID)ID,Name from #T group by Name) b on a.Name=b.Name and a.ID=b.ID where b.Id is null方法3:
delete a from #T a where ID not in (select min(ID) from #T where Name=a.Name)方法4(注:ID为唯一时可用):
delete a from #T a where ID not in(select min(ID)from #T group by Name)方法5:
delete a from #T a where (select count(1) from #T where Name=a.Name and ID<a.ID)>0方法6:
delete a from #T a where ID<>(select top 1 ID from #T where Name=a.name order by ID)方法7:
delete a from #T a where ID>any(select ID from #T where Name=a.Name)select * from #T生成结果:
/*
ID Name Memo
----------- ---- ----
1 A A1
4 B B1(2 行受影响)
*/
--II、Name相同ID保留最大的一条记录:方法1:
delete a from #T a where exists(select 1 from #T where Name=a.Name and ID>a.ID)方法2:
delete a from #T a left join (select max(ID)ID,Name from #T group by Name) b on a.Name=b.Name and a.ID=b.ID where b.Id is null方法3:
delete a from #T a where ID not in (select max(ID) from #T where Name=a.Name)方法4(注:ID为唯一时可用):
delete a from #T a where ID not in(select max(ID)from #T group by Name)方法5:
delete a from #T a where (select count(1) from #T where Name=a.Name and ID>a.ID)>0方法6:
delete a from #T a where ID<>(select top 1 ID from #T where Name=a.name order by ID desc)方法7:
delete a from #T a where ID<any(select ID from #T where Name=a.Name)
select * from #T
/*
ID Name Memo
----------- ---- ----
3 A A3
5 B B2(2 行受影响)
*/
if object_id('[companymap]') is not null drop table [companymap]
go
create table [companymap]([companyid] int,[chainid] int)
insert [companymap]
select 1,1 union all
select 1,2 union all
select 1,3
---删除---
delete companymap
where exists(select 1 from companymap t where t.companyid=companymap.companyid and t.chainid<companymap.chainid)
---查询---
select * from companymap---结果---
companyid chainid
----------- -----------
1 1(所影响的行数为 1 行)
from companymap
group by companyid
delete companymap from companymap t where not exists (select 1 from companymap where companyid = t.companyid and chainid < t.chainid)