求一SQL

[梅子]

表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		NULL

4		2013-03-02	AAA		25		NULL
5		2013-03-02	BBB		12		NULL
6		2013-03-02	CCC		55		NULL

7		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		NULL

4		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)	7

7		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
......

[rucypli]

select A.* ,A.sale-B.sale from tb A left join tb B on date_add(A.time,interval 1 day)=B.time

[WWWWA]

update tt a inner join tt b on a.`Time`=b.`Time`+interval 1 day set a.todaySale=a.sale-b.sale and a.name=b.name

[梅子]

引用 1 楼 rucypli 的回复:

select A.* ,A.sale-B.sale from tb A left join tb B on date_add(A.time,interval 1 day)=B.time

我是要做update操作。。

[WWWWA]

update tt a inner join tt b
on a.`Time`=b.`Time`+interval 1 day and a.name=b.name
set a.todaySale=a.sale-b.sale

[梅子]

谢谢大大们。。。结贴