一个表,主要的字段为:
ip varchar(20),
data integer,
time datetime统计每分钟每个IP的流量,流量为汇总,即从开始记录到当前该IP的总流量。希望得到最近一分钟内的所有IP流量,应该怎样写一条SQL语句得到?(即找到最近的所有IP的数值减去前一分钟该ip的数值)
ip varchar(20),
data integer,
time datetime统计每分钟每个IP的流量,流量为汇总,即从开始记录到当前该IP的总流量。希望得到最近一分钟内的所有IP流量,应该怎样写一条SQL语句得到?(即找到最近的所有IP的数值减去前一分钟该ip的数值)
--这个在SQLServer下没有问题, 至于其他的数据库要看他们自带的时间处理函数了
SELECT COUNT(ip) as IP_TOTAL_IN_ONE_MINUTE
FROM 表 where DateDiff(DateInterval.Minute, time , now)<=1
+------------+------+---------------------+
| ip | data | time |
+------------+------+---------------------+
| 127.0.0.1 | 100 | 2009-07-11 17:11:00 |
| 127.0.0.1 | 145 | 2009-07-11 17:12:00 |
| 127.0.0.1 | 176 | 2009-07-11 17:13:00 |
| 127.0.0.1 | 240 | 2009-07-11 17:14:00 |
| 127.0.0.10 | 267 | 2009-07-11 17:11:00 |
| 127.0.0.10 | 313 | 2009-07-11 17:12:00 |
| 127.0.0.10 | 383 | 2009-07-11 17:13:00 |
| 127.0.0.10 | 448 | 2009-07-11 17:14:00 |
| 127.0.0.2 | 368 | 2009-07-11 17:11:00 |
| 127.0.0.2 | 452 | 2009-07-11 17:12:00 |
| 127.0.0.2 | 509 | 2009-07-11 17:13:00 |
| 127.0.0.2 | 541 | 2009-07-11 17:14:00 |
| 127.0.0.3 | 521 | 2009-07-11 17:11:00 |
| 127.0.0.3 | 543 | 2009-07-11 17:12:00 |
| 127.0.0.3 | 549 | 2009-07-11 17:13:00 |
| 127.0.0.3 | 631 | 2009-07-11 17:14:00 |
| 127.0.0.4 | 568 | 2009-07-11 17:11:00 |
| 127.0.0.4 | 591 | 2009-07-11 17:12:00 |
| 127.0.0.4 | 676 | 2009-07-11 17:13:00 |
| 127.0.0.4 | 755 | 2009-07-11 17:14:00 |
| 127.0.0.5 | 593 | 2009-07-11 17:11:00 |
| 127.0.0.5 | 634 | 2009-07-11 17:12:00 |
| 127.0.0.5 | 667 | 2009-07-11 17:13:00 |
| 127.0.0.5 | 762 | 2009-07-11 17:14:00 |
| 127.0.0.6 | 722 | 2009-07-11 17:11:00 |
| 127.0.0.6 | 794 | 2009-07-11 17:12:00 |
| 127.0.0.6 | 821 | 2009-07-11 17:13:00 |
| 127.0.0.6 | 865 | 2009-07-11 17:14:00 |
| 127.0.0.7 | 800 | 2009-07-11 17:11:00 |
| 127.0.0.7 | 855 | 2009-07-11 17:12:00 |
| 127.0.0.7 | 901 | 2009-07-11 17:13:00 |
| 127.0.0.7 | 971 | 2009-07-11 17:14:00 |
| 127.0.0.8 | 879 | 2009-07-11 17:11:00 |
| 127.0.0.8 | 922 | 2009-07-11 17:12:00 |
| 127.0.0.8 | 984 | 2009-07-11 17:13:00 |
| 127.0.0.8 | 998 | 2009-07-11 17:14:00 |
| 127.0.0.9 | 993 | 2009-07-11 17:11:00 |
| 127.0.0.9 | 1053 | 2009-07-11 17:12:00 |
| 127.0.0.9 | 1125 | 2009-07-11 17:13:00 |
| 127.0.0.9 | 1160 | 2009-07-11 17:14:00 |
+------------+------+---------------------+
40 rows in set (0.00 sec)mysql> select distinct ip,
-> (select data from t_cablink where ip=t.ip order by `time` desc limit 1)
as md1,
-> (select data from t_cablink where ip=t.ip order by `time` desc limit 1,1
) as md2,
-> (select data from t_cablink where ip=t.ip order by `time` desc limit 1)- -> (select data from t_cablink where ip=t.ip order by `time` desc limit 1,1
) as mdx
-> from t_cablink t;
+------------+------+------+------+
| ip | md1 | md2 | mdx |
+------------+------+------+------+
| 127.0.0.1 | 240 | 176 | 64 |
| 127.0.0.10 | 448 | 383 | 65 |
| 127.0.0.2 | 541 | 509 | 32 |
| 127.0.0.3 | 631 | 549 | 82 |
| 127.0.0.4 | 755 | 676 | 79 |
| 127.0.0.5 | 762 | 667 | 95 |
| 127.0.0.6 | 865 | 821 | 44 |
| 127.0.0.7 | 971 | 901 | 70 |
| 127.0.0.8 | 998 | 984 | 14 |
| 127.0.0.9 | 1160 | 1125 | 35 |
+------------+------+------+------+
10 rows in set (0.03 sec)mysql>