50分求一个SQL 语句.. 对大侠们来说不难. 顶也有分!!!!!!!

[jim.ma]

一个表
表名IpLog
字段:ID | NAME | IP | DATEANDTIME

想得到的查询结果是

同一个IP 在一分钟内 访问 相同 NAME 超过60 次的 IP

NAME 可重复 IP 可重复 DATEANDTIME 为GETDATE()

ID 自增 但可以不考虑.

不知道用group by 该怎么写:(

[aojiankd]

不会，帮你顶

[xbstly]

bu hui

[lint]

oracle:
select to_char(DATEANDTIME,'yyyymmddhh24mi'), ip,name,count(*) from iplog group by to_char(DATEANDTIME,'yyyymmddhh24mi'),ip,name
having count(*)>60
sqlserver中把to_char用convert函数改写就可以了，参数参考帮助，。

[ilqtj]

select ip,name from iplog where DATEDIFF(second, DATEANDTIME,(select distinct DATEANDTIME from iplog))<1 group by ip,name having count(*)>60

[ilqtj]

vbs:
要不你select distinct DATEANDTIME from iplog:找出所有不同的登陆时间
让后循环查询
do while not rsdistinct.eof
select ip,name,count(*) as ipcount from iplog where DATEDIFF(second, DATEANDTIME,上面的DATEANDTIME)<1 group by ip,name having count(*)>60
response.write "ip地址：" & rsdistinct(ip) & ",姓名：" & rsdistinct(name) & ",登陆次数：" & & rsdistinct(ipcount)
rsdistinct.movenext
loop

[monkeys]

用存储过程速度快些

在数据量多的时候，你就会身有体会的

[shijian58]

帮你顶，记着给分

[hnjava]

顶！学习中

[iamsangster]

up

[paz]

up~~~

[clq991]

up

[qhdsnow]

这个用建立表关联方法很好做。你查相关书籍上都有，这是基础。

[redstorm11]

up

[klan]

try this:

select A.ip, A.[Name] from
(select [Name],ip
from IpLog
where abs(datediff(mi,getdate(),DATEANDTIME))=1 or abs(datediff(mi,DATEANDTIME,getdate()))=1) A
group by A.ip, A.[Name] having count(*)>60

[61]

SELECT DISTINCT Ip
FROM (SELECT B.name, B.Ip, A.datetime, COUNT(B.Id) AS num
FROM IPLog A INNER JOIN
IPLog B ON A.Id <> B.Id AND A.name = B.name AND A.Ip = B.Ip AND
DATEDIFF(S, A.datetime, B.datetime) < 60 AND
A.datetime < B.datetime
GROUP BY B.Ip, B.name, A.datetime) c
WHERE (num >60)

[fatcat_xp]

顶 学习

[ilqtj]

上面的错了
declare cursor_insert cursor for select distinct DATEANDTIME from iplog
declare @@i datetime
open cursor_insert
fetch cursor_insert into @@i
while @@fetch_status=0
begin
fetch cursor_insert into @@i
select ip,name from iplog where DATEDIFF(second, DATEANDTIME,@@i)<1 group by ip,name having count(*)>60
end
close cursor_insert
deallocate cursor_insert

[jim.ma]

啊 ? 还要用存储过程呀~~