如何更新某两个字段重复值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呢?
[解决办法]
怎么好多完全相同的纪录，是不是应该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相同，估计怎么改都会出错，提示健值不足
------解决方案--------------------
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
[解决办法]
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 行受影响)
[解决办法]
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