查Name相同ID最小的记录的多種方式

--处理表重复记录(查询和删除)
/******************************************************************************************************************************************************
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 行受影响)
*/






--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 行受影响)
*/










--3、删除重复记录没有大小关系时,处理重复值




--> --> (Roy)生成測試數據
 
if not object_id('Tempdb..#T') is null
    drop table #T
Go
Create table #T([Num] int,[Name] nvarchar(1))
Insert #T
select 1,N'A' union all
select 1,N'A' union all
select 1,N'A' union all
select 2,N'B' union all
select 2,N'B'
Go


方法1:
if object_id('Tempdb..#') is not null
    drop table #
Select distinct * into # from #T--排除重复记录结果集生成临时表#


truncate table #T--清空表


insert #T select * from #    --把临时表#插入到表#T中


--查看结果
select * from #T


/*
Num         Name
----------- ----
1           A
2           B


(2 行受影响)
*/


--重新执行测试数据后用方法2
方法2:


alter table #T add ID int identity--新增标识列
go
delete a from  #T a where  exists(select 1 from #T where Num=a.Num and Name=a.Name and ID>a.ID)--只保留一条记录
go
alter table #T drop column ID--删除标识列


--查看结果
select * from #T


/*
Num         Name
----------- ----
1           A
2           B


(2 行受影响)


*/


--重新执行测试数据后用方法3
方法3:
declare Roy_Cursor cursor local for
select count(1)-1,Num,Name from #T group by Num,Name having count(1)>1
declare @con int,@Num int,@Name nvarchar(1)
open Roy_Cursor
fetch next from Roy_Cursor into @con,@Num,@Name
while @@Fetch_status=0
begin 
    set rowcount @con;
    delete #T where Num=@Num and Name=@Name
    set rowcount 0;
    fetch next from Roy_Cursor into @con,@Num,@Name
end
close Roy_Cursor
deallocate Roy_Cursor


--查看结果
select * from #T
/*
Num         Name
----------- ----
1           A
2           B


(2 行受影响)
*/
### 回答1: 如果多张表的字段相同,可以使用 `UNION` 语句将它们合并询。`UNION` 语句可以将多个 `SELECT` 语句的结果合并在一起,生成一个包含所有结果的表。 以下是一个简单的例子,假设我们有三张表 `table1`、`table2` 和 `table3`,它们都包含 `id` 和 `name` 两列: ```sql SELECT id, name FROM table1 UNION SELECT id, name FROM table2 UNION SELECT id, name FROM table3; ``` 在上面的 SQL 语句中,我们使用了三个 `SELECT` 语句,分别从三张表中选择了 `id` 和 `name` 两列。然后我们使用 `UNION` 语句将这三个 `SELECT` 语句的结果合并在一起,并去除了重复的行。最终,我们得到了一个包含所有结果的表。 需要注意的是,使用 `UNION` 语句合并询的表必须具有相同的列数和数据类型。如果两个 `SELECT` 语句的列数不同,或者列的数据类型不同,那么询将会失败。同时,使用 `UNION` 语句进行多张表的合并询时,需要注意性能问题,因为 `UNION` 语句会将所有结果合并在一起,如果数据量很大,询性能可能会很差。 ### 回答2: 在MySQL中,可以通过使用JOIN语句来合并多张表进行询。当多张表具有相同的字段时,可以通过这些字段来进行表之间的连接。 首先,需要确定用于连接表的字段,假设这个字段是id。然后,可以使用INNER JOIN来连接表,并选择需要询的字段。 以下是一个示例询,假设有两张表:表A和表B,它们都有一个id字段且相同,其他字段为name和age。 ``` SELECT A.id, A.name, A.age, B.name, B.age FROM tableA AS A INNER JOIN tableB AS B ON A.id = B.id; ``` 在这个询中,使用了INNER JOIN来连接两张表。使用ON关键字可以指定两个表之间的连接条件,这里使用了id字段来进行连接。通过选择需要询的字段,可以选择指定表的字段。 上述询将返回两张表中id相同记录,并显示表A和表B中的name和age字段。 请注意,除了INNER JOIN外,还有其他类型的连接,例如LEFT JOIN、RIGHT JOIN和FULL JOIN,可以根据具体的需求选择适合的连接方式。 总结起来,使用JOIN语句并指定连接条件,可以合并多张表进行询。通过选择需要询的字段,可以从连接的结果中获取所需的数据。 ### 回答3: 在MySQL中,可以使用多种方法将多张表合并进行询,只需确保这些表具有相同的字段。 一种常用的方法是使用“UNION”操作符来合并询结果。例如,假设我们有两张名为“table1”和“table2”的表,它们有相同的字段(例如,字段名为“id”和“name”),我们可以使用以下询来合并询结果: ``` SELECT id, name FROM table1 UNION SELECT id, name FROM table2; ``` 上述询将返回合并了“table1”和“table2”的结果集。 另一种方法是使用“JOIN”操作符来合并询多个表。使用“JOIN”语句时,必须指定表之间的连接条件。例如,假设我们有两张名为“table1”和“table2”的表,它们有相同的字段(例如,字段名为“id”和“name”),并且它们通过“id”字段进行连接,则可以使用以下询来合并询结果: ``` SELECT t1.id, t1.name, t2.name FROM table1 t1 JOIN table2 t2 ON t1.id = t2.id; ``` 上述询将返回合并了“table1”和“table2”的结果集,并通过“id”字段进行连接。 无论是使用“UNION”操作符还是“JOIN”操作符,都可以根据需要进行嵌套、筛选和排序等操作,以满足具体的询需求。 需要注意的是,在合并询多张表时,必须确保表的结构和字段相同,否则可能会出现错误或者无法得到预期的结果。
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值