update com_info a set a.status=0 where a.com_id in ( select com_id,count(ip) from (select distinct com_id,ip from ipwarn group by com_id) group by com_id having count(ip)>=8)--这是更新语句,自动执行的话要建一JOB的.
一楼的大虾 是不是应该写成distinct ip,com_id 我不太清楚顺便问问
update com_info a set a.status = 0 where 8 <= (select count (distinct ip) from ipwarn where com_id = a.com_id);
select count(distinct(ip)) countip,com_id from ipwarn
group by com_id
having countip>1你说的超过8个之后,要干什么?
而且你的语句中也没有体现出来8啊
com_id ip time
285 127.0.0.1 2004-8-18 10:55:41
285 127.0.0.1 2004-8-18 11:01:01
285 10.200.7.160 2004-8-18 11:05:47
285 10.200.7.160 2004-8-18 11:06:03
285 127.0.0.1 2004-8-18 14:40:31
285 127.0.0.1 2004-8-18 14:43:14
336 127.0.0.1 2004-8-18 15:01:13
337 127.0.0.1 2004-8-18 15:03:45
335 127.0.0.1 2004-8-18 15:12:03
每天晚上12点统计ipwarn,如果同一个com_id有8个不同的ip地址,就修改com_info表中的status=0。自动执行。
set a.status = 0
where 8 <= (select count (distinct ip)
from ipwarn
where com_id = a.com_id);
好东西!又学了一招!