注:不要用date,in这种sql关键字做字段名!!!!!
1、onhand = sum(in)-sum(out) where date <= a specified date
select mydate, qty_in, qty_out,
(select sum(qty_in) - sum(qty_out)
from mytable as x
where x.mydate < = y.mydate) as onhand
from mytable as y;
2、select a,b,c
from mytable as x
where (a,b) in (select a, max(b)
from mytable as y
where y.a = x.a);
1、onhand = sum(in)-sum(out) where date <= a specified date
select mydate, qty_in, qty_out,
(select sum(qty_in) - sum(qty_out)
from mytable as x
where x.mydate < = y.mydate) as onhand
from mytable as y;
2、select a,b,c
from mytable as x
where (a,b) in (select a, max(b)
from mytable as y
where y.a = x.a);
同时,当引用同一个表的时候,用不同的别名能使数据库分辨是从哪个表里拿数据。
select x.field1, y.field2
from mytable1 as x, mytable2 as y
....
等价于
select mytable1.field1, mytable2.filed2
from mytable1,mytable2
select mydate, qty_in, qty_out,
(select sum(qty_in) - sum(qty_out)
from mytable as x
where x.mydate < = y.mydate) as onhand
from mytable as y;
在编译时会报x.mydate < = y.mydate这个表达式有错误,是我错了还是你错了???