34,593
社区成员
发帖
与我相关
我的任务
分享
Create Table #TB(ID Int,Name1 VarChar(10),Name2 VarChar(10))
Insert #TB Select 1,'张三','李四'
Union All Select 2,'张三','李四'
Union All Select 3,'李四','王五'
Union All Select 4,'李四','赵六'
Union All Select 5,'张三','王五'
Union All Select 6,'李四','赵六'
Select Max(ID) AS ID,Name1,Name2 from #TB Group by Name1,Name2
/*
ID Name1 Name2
----------- ---------- ----------
2 张三 李四
3 李四 王五
5 张三 王五
6 李四 赵六
(所影响的行数为 4 行)
*/
select * from tb
where id not in (select min(a.id)
from tb a,tb b
where a.id!=b.id and a.name1=b.name1 and a.name2=b.name2
group by a.name1,a.name2)
if not object_id('tb') is null
drop table tb
Go
Create table tb([ID] int,[name1] nvarchar(2),[name2] nvarchar(2))
Insert tb
select 1,N'张三',N'李四' union all
select 2,N'张三',N'李四' union all
select 3,N'李四',N'王五' union all
select 4,N'李四',N'赵六' union all
select 5,N'张三',N'王五' union all
select 6,N'李四',N'赵六'
Go
select * from
(
select *,
(select count(*)+1 from tb where [ID]>T.[ID] and [name1]=T.[name1] and [name2]=T.[name2]) rank
from tb t
)tt
where rank=1
ID name1 name2 rank
----------- ----- ----- -----------
2 张三 李四 1
3 李四 王五 1
5 张三 王五 1
6 李四 赵六 1
(4 行受影响)
--> --> (wufeng4552)生成測試數據
if not object_id('tb') is null
drop table tb
Go
Create table tb([ID] int,[name1] nvarchar(2),[name2] nvarchar(2))
Insert tb
select 1,N'张三',N'李四' union all
select 2,N'张三',N'李四' union all
select 3,N'李四',N'王五' union all
select 4,N'李四',N'赵六' union all
select 5,N'张三',N'王五' union all
select 6,N'李四',N'赵六'
Go
;with tt
as
(select px=row_number()over(partition by name1,name2 order by id desc),
* from tb)
select ID,
name1,
name2
from tt where px=1 order by id
/*
ID name1 name2
----------- ----- -----
2 张三 李四
3 李四 王五
5 张三 王五
6 李四 赵六
(4 個資料列受到影響)
*/
create table tab(id int,name1 varchar(10),name2 varchar(10))
insert tab
select 1,'张三','李四' union all
select 2,'张三','李四' union all
select 3,'李四','王五' union all
select 4,'李四','赵六' union all
select 5,'张三','王五' union all
select 6,'李四','赵六'
select *
from tab T1
where not exists (select 1 from tab T2 where T2.name1=T1.name1 and T2.name2=T1.name2 and T1.id<T2.id)
drop table tab
(6 row(s) affected)
id name1 name2
----------- ---------- ----------
2 张三 李四
3 李四 王五
5 张三 王五
6 李四 赵六
(4 row(s) affected)
Select a.id,a.name1,a.name2 From tb a Where a.id= (Select max(id) From tb b Where a.name1=b.name1 And a.name2=b.name2 )