select * from exchange as x inner join
(
select id ,(
select sum( cast(share as signed) * cast(exchangedirection as signed)) from exchange where stockid= a.stockid and time<= a.time and id<=a.id
) as lastshare
from exchange as a
) as y on x.id=y.id
update exchange inner join
(
select id ,(
select sum(cast(share as signed) * cast(exchangedirection as signed)) from exchange where stockid= a.stockid and time<= a.time and id<=a.id
) lastshare from exchange as a
) as y on exchange.id=y.id
set exchange.lastshare = y.lastshare where lastshare is null
delimiter //
create trigger tri_exchangeInsert after insert
on exchange for each row
begin
update exchange inner join
(select id ,(select sum( cast(share as signed) * cast(exchangedirection as signed)) from exchange where stockid= a.stockid and time<= a.time and id<=a.id ) lastshare from exchange as a
) as y on exchange.id=y.id
set exchange.lastshare = y.lastshare where lastshare is null;
end;//
delimiter ;
delimiter就是告诉mysql解释器,该段命令是否已经结束了,mysql是否可以执行了。
默认情况下,delimiter是分号;。在命令行客户端中,如果有一行命令以分号结束,
那么回车后,mysql将会执行该命令。如输入下面的语句
mysql> select * from test_table;
然后回车,那么MySQL将立即执行该语句。
但有时候,不希望MySQL这么做。在为可能输入较多的语句,且语句中包含有分号。
默认情况下,不可能等到用户把这些语句全部输入完之后,再执行整段语句。
因为mysql一遇到分号,它就要自动执行。
即,在语句RETURN '';时,mysql解释器就要执行了。
这种情况下,就需要事先把delimiter换成其它符号,如//或$$。