急求一个sql语句,帮帮我吧

hzpike 2009-10-16 09:18:02
如何删除表中重复的数据
比如
id name no
1 aaa 22
2 aaa 22
3 bbb 12
4 bbb 12
5 bbb 12
6 ccc 33
7 ddd gg

去除重复的记录变成
id name no
1 aaa 22
5 bbb 12
6 ccc 33
7 ddd gg
...全文
88 19 打赏 收藏 转发到动态 举报
写回复
用AI写文章
19 条回复
切换为时间正序
请发表友善的回复…
发表回复
ming_Y 2009-10-16
  • 打赏
  • 举报
回复
不可能ID是1,5,6,7了
ming_Y 2009-10-16
  • 打赏
  • 举报
回复
create table tb(id int,name varchar(10),no varchar(10))
insert into tb values(1 , 'aaa' , '22')
insert into tb values(2 , 'aaa' , '22')
insert into tb values(3 , 'bbb' , '12')
insert into tb values(4 , 'bbb' , '12')
insert into tb values(5 , 'bbb' , '12')
insert into tb values(6 , 'ccc' , '33')
insert into tb values(7 , 'ddd' , 'gg')
go


delete tb from tb t where exists(select 1 from tb where name=t.name and no=t.no and id<t.id)

select * from tb

drop table tb
tdtxflsh 2009-10-16
  • 打赏
  • 举报
回复

--创建临时表#保存不重复记录
select distinct * into #test from table1
--删除源表的所有记录
delete from table1
--从临时表插入数据到源表,下面的语句根据需要,列出字段替换...
insert into table1
select *
from #test
--删除临时表#
drop table #test
go
fanzhouqi 2009-10-16
  • 打赏
  • 举报
回复
id name no
1 aaa 22
5 bbb 12
6 ccc 33
7 ddd gg
如果 id 在这边一定要 1和5??
--小F-- 2009-10-16
  • 打赏
  • 举报
回复
----------------------------------------------------------------
-- Author :fredrickhu(我是小F,向高手学习)
-- Date :2009-10-16 09:21:45
-- 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.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([id] int,[name] varchar(3),[no] varchar(2))
insert [tb]
select 1,'aaa','22' union all
select 2,'aaa','22' union all
select 3,'bbb','12' union all
select 4,'bbb','12' union all
select 5,'bbb','12' union all
select 6,'ccc','33' union all
select 7,'ddd','gg'
--------------开始查询--------------------------
delete t from tb t where exists(select 1 from tb where t.no=no and t.Name=Name and t.id<id)
select * from tb
----------------结果----------------------------
/* id name no
----------- ---- ----
2 aaa 22
5 bbb 12
6 ccc 33
7 ddd gg

(4 行受影响)
*/
bancxc 2009-10-16
  • 打赏
  • 举报
回复
delete from tb t 
where not exists(select 1 from tb where t.Department=Department and t.Name=Name and t.id<id)
--小F-- 2009-10-16
  • 打赏
  • 举报
回复
----------------------------------------------------------------
-- Author :fredrickhu(我是小F,向高手学习)
-- Date :2009-10-16 09:21:45
-- 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.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([id] int,[name] varchar(3),[no] varchar(2))
insert [tb]
select 1,'aaa','22' union all
select 2,'aaa','22' union all
select 3,'bbb','12' union all
select 4,'bbb','12' union all
select 5,'bbb','12' union all
select 6,'ccc','33' union all
select 7,'ddd','gg'
--------------开始查询--------------------------
select * from tb t where not exists(select 1 from tb where name=t.name and no=t.no and id>t.id )
----------------结果----------------------------
/* id name no
----------- ---- ----
2 aaa 22
5 bbb 12
6 ccc 33
7 ddd gg

(4 行受影响)
*/
dawugui 2009-10-16
  • 打赏
  • 举报
回复
楼主结果给错了.
dawugui 2009-10-16
  • 打赏
  • 举报
回复
create table tb(id int,name varchar(10),no varchar(10))
insert into tb values(1 , 'aaa' , '22')
insert into tb values(2 , 'aaa' , '22')
insert into tb values(3 , 'bbb' , '12')
insert into tb values(4 , 'bbb' , '12')
insert into tb values(5 , 'bbb' , '12')
insert into tb values(6 , 'ccc' , '33')
insert into tb values(7 , 'ddd' , 'gg')
go

delete tb from tb t where id not in (select min(id) from tb where no = t.no)

select * from tb

drop table tb

/*
id name no
----------- ---------- ----------
1 aaa 22
3 bbb 12
6 ccc 33
7 ddd gg

(所影响的行数为 4 行)
*/
SQL77 2009-10-16
  • 打赏
  • 举报
回复
[Quote=引用 2 楼 dawugui 的回复:]
delete tb from tb t where id not in (select min(id) from tb where no = t.no)
[/Quote]
删除顶乌龟大侠的
rucypli 2009-10-16
  • 打赏
  • 举报
回复
delete from tb
where id not in
(SELECT MIN(ID)FROM TB GROUP BY NAME,NO)
bancxc 2009-10-16
  • 打赏
  • 举报
回复
delete from tb t
where not exists(select 1 from tb where t.name=Name and t.no=no and t.id>id)
SQL77 2009-10-16
  • 打赏
  • 举报
回复
[Quote=引用 3 楼 playwarcraft 的回复:]
怎么aaa留下id=1的,而bbb留下id=5的,没规律?
[/Quote]
是哦,晕,没仔细看,
bancxc 2009-10-16
  • 打赏
  • 举报
回复
delete from tb t
where not exists(select 1 from tb where t.name=no and t.Name=Name and t.id>id)
SQL77 2009-10-16
  • 打赏
  • 举报
回复
SELECT * FROM TB T WHERE ID=(SELECT MIN(ID) FROM TB WHERE NAME=T.NAME AND NO=T.NO)
--小F-- 2009-10-16
  • 打赏
  • 举报
回复
select * from tb t where not exists(select 1 from tb where name=t.name and no=t.no and id>t.id)
playwarcraft 2009-10-16
  • 打赏
  • 举报
回复
怎么aaa留下id=1的,而bbb留下id=5的,没规律?
dawugui 2009-10-16
  • 打赏
  • 举报
回复
delete tb from tb t where id not in (select min(id) from tb where no = t.no)
SQL77 2009-10-16
  • 打赏
  • 举报
回复
SELECT MIN(ID)AS ID ,NAME ,NO FROM TB GROUP BY NAME,NO

34,575

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧