27,579
社区成员
发帖
与我相关
我的任务
分享
select * from
(select row_number() over (Partition By keyId order by keyId) as keyId2,* from A ) as [A2]
where [A2].keyId2 >1
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
*/
不过, 楼主是来搞笑的吧?
两年前的贴了, 楼上那么大神的都不能用?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
*/
select * from
(
select * ,rn=ROW_NUMBER()over(partition by BarCode order by [Time])
from t
) tt where tt.rn=1
)
select a.*
from [表名] a
where not exists(select 1 from [表名] b
where b.BarCode=a.BarCode and b.Time>a.Time)
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
*/