表a如下:
ID datetime IP
1 2009-8-1 14:03:23 192.168.1.56
2 2009-8-2 9:15:06 192.168.1.24
3 2009-8-1 14:07:23 192.168.1.56
4 2009-8-2 9:33:23 192.168.1.24
5 2009-8-1 14:09:23 192.168.1.56
6 2009-8-1 14:11:23 192.168.1.56
求一段统计代码,要求实现同一个IP在datetime相差五分钟以上的算两次,五分钟以内的算一次
ID datetime IP
1 2009-8-1 14:03:23 192.168.1.56
2 2009-8-2 9:15:06 192.168.1.24
3 2009-8-1 14:07:23 192.168.1.56
4 2009-8-2 9:33:23 192.168.1.24
5 2009-8-1 14:09:23 192.168.1.56
6 2009-8-1 14:11:23 192.168.1.56
求一段统计代码,要求实现同一个IP在datetime相差五分钟以上的算两次,五分钟以内的算一次
192.168.1.56 1
192.168.1.24 2
NUM=CASE
WHEN EXISTS
(SELECT 1 FROM TB WHERE IP=T.IP AND ABS(DATEDIFF(MINUTE,[DATETIME],[T.DATETIME]))>5 TEHN 2
WHEN EXISTS
(SELECT 1 FROM TB WHERE IP=T.IP AND ABS(DATEDIFF(MINUTE,[DATETIME],[T.DATETIME]))<5
THEN 1
ELSE 0 END FROM TB T???