SQL过滤重复保留一条

[newnazi]

已知表：

ID , BarCode , Time
1 , 111 , 2016-04-10 10:00:00
2 , 222 , 2016-04-09 09:00:00
3 , 111 , 2016-04-08 08:00:00
4 , 111 , 2016-0410 10:00:00


要求过滤重复 BarCode 并且保留最新一条
如下：
222 , 2016-04-09 09:00:00
111 , 2016-0410 10:00:00

[道素]

            DECLARE @TestTb  Table(ID int,BarCode varchar(100),[Time] datetime)

            insert into @TestTb(ID,BarCode,[Time] )
            select  1  ,'111'   ,   convert(DATETIME,'2016-04-10 10:00:00') union all
            select  2  ,'222'  ,   convert(DATETIME,'2016-04-09 09:00:00') union all
            select  3  ,'111'  ,  convert(DATETIME,'2016-04-08 08:00:00') union all
            select  4  ,'111' ,    convert(DATETIME,'2016-04-10 10:00:00')

         --   select *,row_number()over(PARTITION BY BarCode ORDER BY [time] DESC) as rn FROM @TestTb as t
DELETE tt from( select *,row_number()over(PARTITION BY BarCode ORDER BY [time]) as rn FROM @TestTb as t) as tt where tt.rn!=1
select * from @TestTb
/*
1	111	2016-04-10 10:00:00.000
2	222	2016-04-09 09:00:00.000

*/

[唐诗三百首]

select a.*
  from [表名] a
  where not exists(select 1 from [表名] b 
                               where b.BarCode=a.BarCode and b.Time>a.Time)

[spiritofdragon]

select * from 
(
select * ,rn=ROW_NUMBER()over(partition by BarCode order by [Time])
from t
) tt where tt.rn=1
)

[Ekun_sky]

with a(ID,BarCode,Time) as(
select 1,111,'2016-04-10 10:00:00' union all
select 2,222,'2016-04-09 09:00:00' union all
select 3,111,'2016-04-08 08:00:00' union all
select 4,111,'2016-0410 10:00:00')


select BarCode,MAX(TIME) AS ATIME from a GROUP BY BarCode

/*
ID    BarCode   Time
-----------------------
111	2016-04-10 10:00:00
222	2016-04-09 09:00:00
*/

[中国风]

处理表重复记录(查询和删除) http://bbs.csdn.net/topics/240034273

[中国风]

结果集只是查询两列 用#3方法就行了

[newnazi]

大神们还有什么好的方法建议？？？？？？？？？？？？

[吉普赛的歌]

DECLARE @t TABLE (
	id INT,
	barcode INT,
	[time] DATETIME
)
INSERT INTO @t(id,barcode,[time])
	      SELECT 1,111,'2016-04-10 10:00:00'
UNION ALL SELECT 2,222,'2016-04-09 09:00:00'
UNION ALL SELECT 3,111,'2016-04-08 08:00:00'
UNION ALL SELECT 4,111,'2016-04-10 10:00:00'

;WITH t AS (
	SELECT ROW_NUMBER() OVER (PARTITION BY barcode ORDER BY [time] DESC) AS rid,* 
	FROM @t
)
DELETE FROM t WHERE rid!=1

SELECT * FROM @t
/*
id	barcode	time
1	111	    2016-04-10 10:00:00.000
2	222	    2016-04-09 09:00:00.000
*/

不过， 楼主是来搞笑的吧？ 两年前的贴了， 楼上那么大神的都不能用？

[OwenZeng_DBA]

忘记结帖了而已吧，，，

[吉普赛的歌]

引用 9 楼 z10843087 的回复:

忘记结帖了而已吧，，，

不是。 你看楼主在 7 楼还在求建议

[听雨停了]

引用 10 楼 yenange 的回复:

[quote=引用 9 楼 z10843087 的回复:] 忘记结帖了而已吧，，，

不是。 你看楼主在 7 楼还在求建议[/quote] LZ智商余额不足，有待充值

[qq_25073223]

参阅以下文章: http://www.maomao365.com/?p=4942 将删除修改为 查询就可以了

select *  from 
(select row_number() over (Partition By keyId order by keyId) as keyId2,* from A ) as [A2]
where [A2].keyId2  >1