delete 表 where exists(select 1 from 表 as A where A.name=表.name and A.[year]<表.[year])
create table #UU1 ( [name] nvarchar(20), [year] int ) insert into #UU1 select 'a',2008 insert into #UU1 select 'a',2009 insert into #UU1 select 'b',2010 insert into #UU1 select 'b',2011delete U from #UU1 U where exists(select * from #UU1 where [name]=U.[name] and [year]<U.[year])select * from #UU1
create table #UU1 ( [name] nvarchar(20), [year] int ) insert into #UU1 select 'a',2008 insert into #UU1 select 'a',2009 insert into #UU1 select 'b',2010 insert into #UU1 select 'b',2011delete U from #UU1 U where exists(select * from #UU1 where [name]=U.[name] and [year]<U.[year])select * from #UU1name year -------------------- ----------- a 2008 b 2010
select * from TB T where not exists (select 1 from TB where T.[name]=[name] And T.[year]<[year] )
select * from [table] t where [year]=(select max([year]) from [table] where name=t.name)
select identity(int,1,1) as autoID, * into #Tmp from table1 select min(autoID) as autoID into #Tmp2 from #Tmp group by Name,autoID select * from #Tmp where autoID in(select autoID from #tmp2)我用这种方法不知道为什么不行?
select name,min(year) from 表table group by name就这么简单。
Hi,create table #UU1 ( [name] nvarchar(20), [year] int ) insert into #UU1 select 'a',2008 insert into #UU1 select 'a',2009 insert into #UU1 select 'b',2010 insert into #UU1 select 'b',2011BEGIN TRAN--删重语句DELETE FROM U FROM #UU1 U (SELECT name,MIN(year) AS 'year' FROM #UU1 GROUP BY name )A ON U.name = A.name AND U.year = A.year WHERE A.name IS NULL AND A.year IS NULL--测试 SELECT * FROM #UU1ROLLBACK TRAN
if object_id('oldTable') is not null drop table oldTable go create table oldTable ( [name] nvarchar(20), [year] int ) insert into oldTable select 'a',2008 insert into oldTable select 'a',2009 insert into oldTable select 'b',2010 insert into oldTable select 'b',2011select * from oldTableif object_id('newTable') is not null drop table newTable go create table newTable ( [name] nvarchar(20), [year] int ) insert into newTable select name,min(year) from oldTable group by name select * from newTable
select name,max(year) from 表table group by name
select name,max(year) from 表table group by name 分组表达式能实现的。
where exists(select 1 from 表 as A where A.name=表.name and A.[year]<表.[year])
(
[name] nvarchar(20),
[year] int
)
insert into #UU1 select 'a',2008
insert into #UU1 select 'a',2009
insert into #UU1 select 'b',2010
insert into #UU1 select 'b',2011delete U from #UU1 U
where exists(select * from #UU1 where [name]=U.[name] and [year]<U.[year])select * from #UU1
(
[name] nvarchar(20),
[year] int
)
insert into #UU1 select 'a',2008
insert into #UU1 select 'a',2009
insert into #UU1 select 'b',2010
insert into #UU1 select 'b',2011delete U from #UU1 U
where exists(select * from #UU1 where [name]=U.[name] and [year]<U.[year])select * from #UU1name year
-------------------- -----------
a 2008
b 2010
*
from
[table] t
where
[year]=(select max([year]) from [table] where name=t.name)
select min(autoID) as autoID into #Tmp2 from #Tmp group by Name,autoID
select * from #Tmp where autoID in(select autoID from #tmp2)我用这种方法不知道为什么不行?
name year
a 2008
a 2009
b 2010
b 2011
我的源表iacc字段有: iacactno,iacprocod,iacitmno,iacamt,iacopndat,....
iacactno有很多重复记录。
插到新表iac,字段和iacc一样,但是iacactno是主键,重复记录插不进去的。所以想把iacactno重复的删掉,只保留一条。
(
[name] nvarchar(20),
[year] int
)
insert into #UU1 select 'a',2008
insert into #UU1 select 'a',2009
insert into #UU1 select 'b',2010
insert into #UU1 select 'b',2011BEGIN TRAN--删重语句DELETE FROM U
FROM #UU1 U
(SELECT name,MIN(year) AS 'year' FROM #UU1
GROUP BY name
)A
ON U.name = A.name AND U.year = A.year
WHERE A.name IS NULL AND A.year IS NULL--测试
SELECT * FROM #UU1ROLLBACK TRAN
go
create table oldTable
(
[name] nvarchar(20),
[year] int
)
insert into oldTable select 'a',2008
insert into oldTable select 'a',2009
insert into oldTable select 'b',2010
insert into oldTable select 'b',2011select * from oldTableif object_id('newTable') is not null drop table newTable
go
create table newTable
(
[name] nvarchar(20),
[year] int
)
insert into newTable
select name,min(year)
from oldTable group by name select * from newTable
select name,max(year) from 表table group by name