create table zbh (记录内容 varchar(10), 发生时间 varchar(30))insert into zbh select 'a1','2014-11-30 10:30:52' union all select 'a2','2014-11-30 10:30:52' union all select 'a3','2014-11-30 10:30:52' union all select 'qq','2014-11-30 10:35:42' union all select 'pq','2014-11-30 10:35:48' union all select 'a4','2014-11-30 10:30:52' union all select 'a5','2014-11-30 10:30:52' delete a from zbh a inner join (select 发生时间 from zbh group by 发生时间 having count(1)>=5) b on a.发生时间=b.发生时间select * from zbh/* 记录内容 发生时间 ---------- ------------------------------ qq 2014-11-30 10:35:42 pq 2014-11-30 10:35:48(2 行受影响) */
delete aa from tablename as aa where exists(select 1 from tablename as bb where aa.发生时间=bb.发生时间 group by bb.发生时间 having COUNT(*)>=5)
WITH cte AS( select * from (SELECT ROW_NUMBER()OVER(PARTITION BY [发生时间],order by [发生时间])a,* from Temp_Time)b ) DELETE FROM cte WHERE a>=5
;WITH cte AS( SELECT * from (SELECT count(1)OVER(PARTITION BY Timea)a,* from Temp_Time)b ) DELETE FROM cte WHERE a>=5
create table zbh
(记录内容 varchar(10),
发生时间 varchar(30))insert into zbh
select 'a1','2014-11-30 10:30:52' union all
select 'a2','2014-11-30 10:30:52' union all
select 'a3','2014-11-30 10:30:52' union all
select 'qq','2014-11-30 10:35:42' union all
select 'pq','2014-11-30 10:35:48' union all
select 'a4','2014-11-30 10:30:52' union all
select 'a5','2014-11-30 10:30:52'
delete a
from zbh a
inner join (select 发生时间
from zbh
group by 发生时间
having count(1)>=5) b on a.发生时间=b.发生时间select * from zbh/*
记录内容 发生时间
---------- ------------------------------
qq 2014-11-30 10:35:42
pq 2014-11-30 10:35:48(2 行受影响)
*/
delete aa from tablename as aa where exists(select 1 from tablename as bb where aa.发生时间=bb.发生时间 group by bb.发生时间 having COUNT(*)>=5)
select * from (SELECT ROW_NUMBER()OVER(PARTITION BY [发生时间],order by [发生时间])a,* from Temp_Time)b
)
DELETE FROM cte WHERE a>=5
SELECT * from (SELECT count(1)OVER(PARTITION BY Timea)a,* from Temp_Time)b
)
DELETE FROM cte WHERE a>=5