如何将两段SQL语句合成一段SQL语句？

[mylajilaji]

已有数据（表名stock）
mydate code number price
2008-3-11 600418 500 8
2008-3-11 600221 300 9
2008-3-12 600418 600 8.1
2008-3-12 600221 700 8.9
2008-3-13 600418 800 7.5
2008-3-13 600221 1000 8.3


需要生成下面的表格：

mydate totalasset increase
2008-3-11
2008-3-12
2008-3-13
字段的定义：
Totalasset= sum(number*price)
Increase=本日的Totalasset/上一日的Totalasset-1

我的做法是：
1、先产生mydate、totalasset字段

SELECT mydate, sum(stock.number*stock.price) as totalasset
INTO TEMP
FROM stock
GROUP BY mydate;
先生成

mydate totalasset
2008-3-11 6700
2008-3-12 11090
2008-3-13 14300


2、然后产生increase字段
SELECT t1.mydate,t1.totalasset,t2.totalasset/t1.totalasset-1 as increase
FROM temp t1 left JOIN temp t2 ON t1.mydate = t2.mydate+1;

生成下面的表

mydate totalasset increase
11-Mar-08 6700 　
12-Mar-08 11090 -0.40
13-Mar-08 14300 -0.22


如何将两段sql语句合成一段完成我的需求？

[liuyann]

select a.mydate,a.tsum,a.tsum/b.tsum-1 as increase
from (select mydate,sum(number*price) as tsum from stock group by mydate) a
	left join
	(select mydate,sum(number*price) as tsum from stock group by mydate) b
	on a.mydate=b.mydate+1

== 思想重于技巧 ==

[liuyann]

你在做什么系统?

有兴趣的话，可以加入这个新群
19055578 - CSDN-VBA&Access

== 思想重于技巧 ==

[WWWWA]

分组后再自连接即可，
select a.mydate,a.tsum,a.tsum/b.tsum-1 as increase from (select mydate,sum(number*price) as tsum from stock group by mydate) a left join (select mydate,sum(number*price) as tsum from stock group by mydate) b on a.mydate=b.mydate+1