表A结构如下:+------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+----------------+
| idx | int(11) | NO | PRI | NULL | auto_increment |
| Time | date | YES | | NULL | |
| Name | varchar(200) | YES | | NULL | |
| sale | int(11) | YES | | NULL | |
| todaySale | int(11) | YES | | NULL | |
+------------+--------------+------+-----+---------+----------------+
数据如下:idx Time Name sale todaySale
1 2013-03-01 AAA 22 NULL
2 2013-03-01 BBB 10 NULL
3 2013-03-01 CCC 48 NULL4 2013-03-02 AAA 25 NULL
5 2013-03-02 BBB 12 NULL
6 2013-03-02 CCC 55 NULL7 2013-03-03 AAA 25 NULL
8 2013-03-03 BBB 15 NULL
9 2013-03-03 CCC 56 NULL
......
要做的是更新todaySale,得到该商品,当天与上一天的差。
要求得到的结果是:idx Time Name sale todaySale
1 2013-03-01 AAA 22 NULL
2 2013-03-01 BBB 10 NULL
3 2013-03-01 CCC 48 NULL4 2013-03-02 AAA 25(25-22) 3
5 2013-03-02 BBB 12(12-10) 2
6 2013-03-02 CCC 55(55-48) 77 2013-03-03 AAA 25(25-25) 0
8 2013-03-03 BBB 15(15-12) 3
9 2013-03-03 CCC 56(56-55) 1
......sql
| Field | Type | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+----------------+
| idx | int(11) | NO | PRI | NULL | auto_increment |
| Time | date | YES | | NULL | |
| Name | varchar(200) | YES | | NULL | |
| sale | int(11) | YES | | NULL | |
| todaySale | int(11) | YES | | NULL | |
+------------+--------------+------+-----+---------+----------------+
数据如下:idx Time Name sale todaySale
1 2013-03-01 AAA 22 NULL
2 2013-03-01 BBB 10 NULL
3 2013-03-01 CCC 48 NULL4 2013-03-02 AAA 25 NULL
5 2013-03-02 BBB 12 NULL
6 2013-03-02 CCC 55 NULL7 2013-03-03 AAA 25 NULL
8 2013-03-03 BBB 15 NULL
9 2013-03-03 CCC 56 NULL
......
要做的是更新todaySale,得到该商品,当天与上一天的差。
要求得到的结果是:idx Time Name sale todaySale
1 2013-03-01 AAA 22 NULL
2 2013-03-01 BBB 10 NULL
3 2013-03-01 CCC 48 NULL4 2013-03-02 AAA 25(25-22) 3
5 2013-03-02 BBB 12(12-10) 2
6 2013-03-02 CCC 55(55-48) 77 2013-03-03 AAA 25(25-25) 0
8 2013-03-03 BBB 15(15-12) 3
9 2013-03-03 CCC 56(56-55) 1
......sql
select A.* ,A.sale-B.sale
from tb A left join tb B on date_add(A.time,interval 1 day)=B.time
on a.`Time`=b.`Time`+interval 1 day
set a.todaySale=a.sale-b.sale and a.name=b.name
on a.`Time`=b.`Time`+interval 1 day and a.name=b.name
set a.todaySale=a.sale-b.sale