Order表里有 如下几个字段:order.trans, order.status, order.orderId, order.OrderName
OrderALLOC表里有如下子段: OrderALLOC.exec_qty,order_id
想建的视图有如下列 OrderID, OrderName, 数量
数量的设法为 order.status='ready' 时
order.trans='buy'的所有记录的OrderALLOC.exec_qty字段的和(SUM)与
order.trans='sell'的所有记录的OrderALLOC.exec_qty字段的和(SUM)相减 order.status='ready' 以外的情况,数量设空 两个表用 order_id 关联 请高手帮帮忙哦
OrderALLOC表里有如下子段: OrderALLOC.exec_qty,order_id
想建的视图有如下列 OrderID, OrderName, 数量
数量的设法为 order.status='ready' 时
order.trans='buy'的所有记录的OrderALLOC.exec_qty字段的和(SUM)与
order.trans='sell'的所有记录的OrderALLOC.exec_qty字段的和(SUM)相减 order.status='ready' 以外的情况,数量设空 两个表用 order_id 关联 请高手帮帮忙哦
case order.status when 'ready' then
( select sum(t.c_number) from
(
select sum(rderALLOC.exec_qty) as c_number
from OrderALLOC c,order o
where c.order_id=o.order_id and o.status='ready' and o.trans='sell'
union all
select -sum(rderALLOC.exec_qty) as c_number
from OrderALLOC c,order o
where c.order_id=o.order_id and o.status='ready' and o.trans='buy'
) t
)
else 0
from OrderALLOC c,order o
where c.order_id=o.order_id
select sum(t.c_number) from
(
select sum(rderALLOC.exec_qty) as c_number
from OrderALLOC c,order o
where c.order_id=o.order_id and o.status='ready' and o.trans='sell'
union all
select -sum(rderALLOC.exec_qty) as c_number
from OrderALLOC c,order o
where c.order_id=o.order_id and o.status='ready' and o.trans='buy'
) t