表结构和数据如下:
mysql> select id,date,count from STAT;
+------+---------------------+-------+
| id | date | count |
+------+---------------------+-------+
| 1 | 2008-01-24 00:00:00 | 10 |
| 2 | 2008-01-23 00:00:00 | 20 |
| 2 | 2008-01-24 00:00:00 | 35 |
| 3 | 2008-01-23 00:00:00 | 135 |
| 3 | 2008-01-24 00:00:00 | 330 |
+------+---------------------+-------+想找出今天count减昨天count的差大于100的id,请问怎么写效率比较高呢?
mysql> select id,date,count from STAT;
+------+---------------------+-------+
| id | date | count |
+------+---------------------+-------+
| 1 | 2008-01-24 00:00:00 | 10 |
| 2 | 2008-01-23 00:00:00 | 20 |
| 2 | 2008-01-24 00:00:00 | 35 |
| 3 | 2008-01-23 00:00:00 | 135 |
| 3 | 2008-01-24 00:00:00 | 330 |
+------+---------------------+-------+想找出今天count减昨天count的差大于100的id,请问怎么写效率比较高呢?
from STAT a
where exists (select 1
from STAT b
where b.id=a.id
and b.date = a.date-1
and b.count < a.count - 100 )
b.date = a.date-1这个地方不对,要用DATEDIFF(b.date,a.date)=1
from STAT a
where exists (select 1
from STAT b
where b.id=a.id
and DATEDIFF(a.data ,b.data)=1---修改楼上的。
and b.count < a.count - 100 )