如何更新某两个字段重复值10次以上的记录

表tabel1中有多个字段,其它remoteip和clickdate字段重复10次以上的,只保留十条记录的flag = 0其它的flag字段值更新为1请问要怎么写呢?
比如
id     remoteip    clickdate   flag   other
1      127.0.0.1    2007-8-1     0      itads23
1      127.0.0.1    2007-8-1     0      itads232
1      127.0.0.1    2007-8-1     0      itads23
1      127.0.0.1    2007-8-1     0      itads3232
1      127.0.0.1    2007-8-1     0      itads23
1      127.0.0.1    2007-8-1     0      itads32
1      127.0.0.1    2007-8-1     0      itads23
1      127.0.0.1    2007-8-1     0      itads
1      127.0.0.1    2007-8-1     0      itads
1      127.0.0.1    2007-8-1     0      itads
1      127.0.0.1    2007-8-1     0      itads
1      127.0.0.1    2007-8-1     0      itads
1      127.0.0.1    2007-8-1     0      itads
1      127.0.0.1    2007-8-1     0      itads
1      127.0.0.2    2007-8-1     0      itads
1      127.0.0.2    2007-8-1     0      itads

这个表如何写SQL可以使得127.0.0.1 2007-8-1只保留10条flag =0 其它的记录flag记录更新为1呢?

11 个解决方案

#1

怎么好多完全相同的纪录，是不是应该id不同的？

如果id不同：
update a
set flag=1
from tabel1 a
where flag=0
and (select count(*) from table1 where remoteip=a.remoteip and clickdate=a.clickdate and flag=0 and id<=a.id)>10

如果id相同，估计怎么改都会出错，提示健值不足

#2

id是不一样的.我刚才忘了更新了
id是自动编号的

#3

一楼的这个不行哦

#4

update a set flag=0
where table1 a ,(select remoteip,clickdate
from table1 group by remoteip,clickdate
having count(1)>=10) b
where a.remoteip=b.remoteip and a.clickdate=b.clickdate

#5

create table table1(id int identity ,remoteip varchar(10),clickdate varchar(10),flag bit,other varchar(12))
insert into table1 select '127.0.0.1','2007-8-1','0','itads23'
insert into table1 select '127.0.0.1','2007-8-1','0','itads23'
insert into table1 select '127.0.0.1','2007-8-1','0','itads23'
insert into table1 select '127.0.0.1','2007-8-1','0','itads23'
insert into table1 select '127.0.0.1','2007-8-1','0','itads23'
insert into table1 select '127.0.0.1','2007-8-1','0','itads23'
insert into table1 select '127.0.0.1','2007-8-1','0','itads23'
insert into table1 select '127.0.0.1','2007-8-1','0','itads23er'
insert into table1 select '127.0.0.1','2007-8-1','0','itads23se'
insert into table1 select '127.0.0.1','2007-8-1','0','itads23d'
insert into table1 select '127.0.0.1','2007-8-1','0','itads23d'
insert into table1 select '127.0.0.2','2007-8-1','0','itads23fsdd'
insert into table1 select '127.0.0.2','2007-8-1','0','itads23'
insert into table1 select '127.0.0.2','2007-8-1','0','itads23d'
insert into table1 select '127.0.0.2','2007-8-1','0','itads23d'

update a set a.flag=1
from  table1 a ,(select remoteip,clickdate
from table1 group by remoteip,clickdate
having count(1)>=10) b
where a.remoteip=b.remoteip and a.clickdate=b.clickdate

----
id          remoteip   clickdate  flag  other
----------- ---------- ---------- ----- ------------
1           127.0.0.1  2007-8-1   1     itads23
2           127.0.0.1  2007-8-1   1     itads23
3           127.0.0.1  2007-8-1   1     itads23
4           127.0.0.1  2007-8-1   1     itads23
5           127.0.0.1  2007-8-1   1     itads23
6           127.0.0.1  2007-8-1   1     itads23
7           127.0.0.1  2007-8-1   1     itads23
8           127.0.0.1  2007-8-1   1     itads23er
9           127.0.0.1  2007-8-1   1     itads23se
10          127.0.0.1  2007-8-1   1     itads23d
11          127.0.0.1  2007-8-1   1     itads23d
12          127.0.0.2  2007-8-1   0     itads23fsdd
13          127.0.0.2  2007-8-1   0     itads23
14          127.0.0.2  2007-8-1   0     itads23d
15          127.0.0.2  2007-8-1   0     itads23d

(15 行受影响)

#6

to ojuju10(longdchuanren)

#7

我并不是要把超过10条重复的全部更新为1而是把超过10的部分更新为1,
就是要保留10重复的记录的

#8

如果有重复10以上,则保留10flag的值为0其它的更新为1
明白吗,再帮一下

#9

update t set t.flag =1 from table1 t ,
(select top 1 c.id,c.remoteip,c.clickdate from
(select top 10 a.id,a.remoteip,a.clickdate from table1 a join (select remoteip,clickdate ,count(*) as num from table1 group by remoteip,clickdate having count(*) > =10) b
on a.remoteip = b.remoteip and a.clickdate = b.clickdate) as c order by id desc)d
where t.remoteip = d.remoteip and t.clickdate =d.clickdate and t.id > d.id

#10

to lllyyymmm(因丑被判无期徒刑)
还是不行啊.偶试了语法没错,但执行完还是没把超过10条的记录FLAG 置为1

#11

终于搞定了!!!谢谢各位帮忙...
后来偶自己弄着弄着可以了..还是很感谢大家
这个问题主要是取出分组后的前N条记录,再对不在这个范围内的ID字段相应的记录进行更新

第一个视图先取重复键值的大于N条的记录
视图名为vTest
SELECT TOP 100 PERCENT *
FROM dbo.AccessRpt a
WHERE ((SELECT COUNT(*)
          FROM AccessRpt
          WHERE remoteip = a.remoteip AND HitDate = a.HitDate) > 3)
ORDER BY RemoteIp, HitDate DESC

第二个视图用来取每个分组的前N条
视图名为vTestTopN

SELECT *
FROM dbo.vTest a
WHERE (ID IN
          (SELECT TOP 5 id
         FROM vTest
         WHERE hitdate = a.hitdate AND remoteip = a.remoteip
         ORDER BY hitdate, remoteip DESC))

更新语句
update AccessRpt set DelFlag = 1 where id not in (select id from vTestTopN)

#1

#2