问题
已有数据表(假定表名为t)
time sale
1999 484490467
2000 651413668.9
2001 1371371008
2002 1817741625
2003 2505332095
2004 3765438486
2005 4817720384
2006 6083322598
需要产生如下的数据表
time sale incrseace
1999 484490467
2000 651413668.9 0.34
2001 1371371008 1.11
2002 1817741625 0.33
2003 2505332095 0.38
2004 3765438486 0.50
2005 4817720384 0.28
2006 6083322598 0.26
如何用SQL 实现?
SELECT a.time, a.sale, ((a.sale / b.sale) - 1) as growth
from t a, t b
where a.time=b.time+1;
上面的SQL语句将产生
time sale growth
2000 651413668.9 0.34
2001 1371371008 1.11
2002 1817741625 0.33
2003 2505332095 0.38
2004 3765438486 0.50
2005 4817720384 0.28
2006 6083322598 0.26
(我需要保留1999的数据,缺少了一行)
SELECT b.time, b.sale, ((a.sale / b.sale) - 1) as growth
from t a, t b
where a.time=b.time+1;
上面的SQL语句将产生
time sale growth
1999 484490467 0.34
2000 651413668.9 1.11
2001 1371371008 0.33
2002 1817741625 0.38
2003 2505332095 0.50
2004 3765438486 0.28
2005 4817720384 0.26
都不符合结果
请问如何写出合适的SQL?