取出重复记录中的一条

[bychgh]

如何取出数据库重复记录，比如：
id name
1 aa
2 bb
3 aa
4 cc
5 dd
6 ee
7 cc
8 mm
那么取出的结果是
id name
1 aa

3 aa
4 cc


7 cc

[htl258_Tony]

select * from tb t where id=(select min(id) from tb where name=t.name)

[htl258_Tony]

select * from tb t where not exists(select 1 from tb where name=t.name and id<t.id)

[sql_sf]

太多了
不写了

[fsse2000]

select * from Table where Name in
(Select Name from Table group by Name having Count(ID)>1 )

[wwqcumt]

想下东西 不知道这能加积分不

[黄_瓜]

[Quote=引用 5 楼 wwqcumt 的回复:]
想下东西 不知道这能加积分不
[/Quote]
不能，那个分需要用可用分换

[htl258_Tony]

/*------------------------------------------------------------------
--  Author : htl258(Tony)
--  Date   : 2010-04-16 14:15:23
--  Version: Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86) 
	Jul  9 2008 14:43:34 
	Copyright (c) 1988-2008 Microsoft Corporation
	Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)

------------------------------------------------------------------*/
--> 生成测试数据表:tb

IF OBJECT_ID('[tb]') IS NOT NULL
	DROP TABLE [tb]
GO
CREATE TABLE [tb]([id] INT,[name] NVARCHAR(10))
INSERT [tb]
SELECT 1,'aa' UNION ALL
SELECT 2,'bb' UNION ALL
SELECT 3,'aa' UNION ALL
SELECT 4,'cc' UNION ALL
SELECT 5,'dd' UNION ALL
SELECT 6,'ee' UNION ALL
SELECT 7,'cc' UNION ALL
SELECT 8,'mm'
GO
--SELECT * FROM [tb]

-->SQL查询如下:
select * from tb t where exists(select 1 from tb where name=t.name and id<>t.id)
/*
id          name
----------- ----------
1           aa
3           aa
4           cc
7           cc

(4 行受影响)
*/

不好意思，看反了

[--小F--]

--处理表重复记录(查询和删除)
/******************************************************************************************************************************************************
1、Num、Name相同的重复值记录,没有大小关系只保留一条
2、Name相同,ID有大小关系时,保留大或小其中一个记录
整理人：中国风(Roy)

日期:2008.06.06
******************************************************************************************************************************************************/

--1、用于查询重复处理记录(如果列没有大小关系时2000用生成自增列和临时表处理,SQL2005用row_number函数处理)

--> --> (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),方法3在SQl05时，效率高于1、2
方法1:
Select * from #T a where not exists(select 1 from #T where Name=a.Name and ID<a.ID)

方法2:
select a.* from #T a join (select min(ID)ID,Name from #T group by Name) b on a.Name=b.Name and a.ID=b.ID

方法3:
select * from #T a where ID=(select min(ID) from #T where Name=a.Name)

方法4:
select a.* from #T a join #T b on a.Name=b.Name and a.ID>=b.ID group by a.ID,a.Name,a.Memo having count(1)=1 

方法5:
select * from #T a group by ID,Name,Memo having ID=(select min(ID)from #T where Name=a.Name)

方法6:
select * from #T a where (select count(1) from #T where Name=a.Name and ID<a.ID)=0

方法7:
select * from #T a where ID=(select top 1 ID from #T where Name=a.name order by ID)

方法8:
select * from #T a where ID!>all(select ID from #T where Name=a.Name)

方法9(注:ID为唯一时可用):
select * from #T a where ID in(select min(ID) from #T group by Name)

--SQL2005:

方法10:
select ID,Name,Memo from (select *,min(ID)over(partition by Name) as MinID from #T a)T where ID=MinID

方法11:

select ID,Name,Memo from (select *,row_number()over(partition by Name order by ID) as MinID from #T a)T where MinID=1

生成结果:
/*
ID          Name Memo
----------- ---- ----
1           A    A1
4           B    B1

(2 行受影响)
*/


--II、Name相同ID最大的记录,与min相反:
方法1:
Select * from #T a where not exists(select 1 from #T where Name=a.Name and ID>a.ID)

方法2:
select a.* from #T a join (select max(ID)ID,Name from #T group by Name) b on a.Name=b.Name and a.ID=b.ID order by ID

方法3:
select * from #T a where ID=(select max(ID) from #T where Name=a.Name) order by ID

方法4:
select a.* from #T a join #T b on a.Name=b.Name and a.ID<=b.ID group by a.ID,a.Name,a.Memo having count(1)=1 

方法5:
select * from #T a group by ID,Name,Memo having ID=(select max(ID)from #T where Name=a.Name)

方法6:
select * from #T a where (select count(1) from #T where Name=a.Name and ID>a.ID)=0

方法7:
select * from #T a where ID=(select top 1 ID from #T where Name=a.name order by ID desc)

方法8:
select * from #T a where ID!<all(select ID from #T where Name=a.Name)

方法9(注:ID为唯一时可用):
select * from #T a where ID in(select max(ID) from #T group by Name)

--SQL2005:

方法10:
select ID,Name,Memo from (select *,max(ID)over(partition by Name) as MinID from #T a)T where ID=MinID

方法11:
select ID,Name,Memo from (select *,row_number()over(partition by Name order by ID desc) as MinID from #T a)T where MinID=1

生成结果2:
/*
ID          Name Memo
----------- ---- ----
3           A    A3
5           B    B2

(2 行受影响)
*/

[喜-喜]

--------------------SQL Server数据格式化工具-------------------
---------------------------------------------------------------
-- DESIGNER :happycell188(喜喜)
--       QQ :584738179
-- Development Tool :Microsoft Visual C++ 6.0    C Language 
-- FUNCTION :CONVERT DATA TO T-SQL
---------------------------------------------------------------
-- Microsoft SQL Server  2005
-- Developer Edition on Microsoft Windows XP [版本 5.1.2600]
---------------------------------------------------------------
---------------------------------------------------------------

use test
go
if object_id('test.dbo.tb') is not null drop table tb
-- 创建数据表
create table tb
(
id int,
name char(3)
)
go
--插入测试数据
insert into tb select 1,'aa'
union all select 2,'bb'
union all select 3,'aa'
union all select 4,'cc'
union all select 5,'dd'
union all select 6,'ee'
union all select 7,'cc'
union all select 8,'mm'
go
--代码实现

select t.id,name from tb t
where exists(select * from tb where t.name=name and t.id<>id)

/*结果
id  name
--------------
1	aa 
3	aa 
4	cc 
7	cc 
*/

[wxlcc520]

create table #temp
(id varchar(1),
name varchar(10))

insert into #temp
select '1','aa'
union select '2','bb'
union select '3','aa'
union select '4','cc'
union select '5','dd'
union select '6','ee'
union select '7','cc'
union select '8','mm'

select a.id,a.name from #temp a
inner join #temp b on a.name=b.name and a.id<>b.id
order by a.id