Interbase SQL重做选择查询

Question

我需要你的帮助。

我有一个Interbase SQL Select：

从aps中选择aps.fullname、aps.productvcode、aps.alccode、aps.capacity、Cast( wp.quantity *wp.Capability/10 where wp.quantity * wp.capacity end)作为小数(18，4)作为WBTotal，其中wp.alccode='0001821000001389010‘组由aps.fullname、aps.productvcode、aps.alccode、aps.capacity组成 联合所有 从aps中选择aps.fullname、aps.productvcode、aps.alccode、aps.capacity、Cast( wp.quantity *wp.Capability/10 where wp.quantity * wp.capacity end)作为小数(18，4)作为WBTotal，其中wp.alccode='0001821000001389010‘组由aps.fullname、aps.productvcode、aps.alccode、aps.capacity组成

它给了我这个：

​

但我希望第二行中的值"156,9750“在"WBTOTAL"旁边的右边是一个单独的列，并有标题为”WBOTOTAL“的标题

该怎么做呢？

Answer 1

您可以使用CTE，如下所示

with CTE1 as
(
    Select aps.fullname, aps.productvcode, aps.alccode, aps.capacity, Cast(Round(Sum(case when wp.capacity>0 then wp.quantity * wp.capacity/10 else wp.quantity * wp.capacity end),4) as DECIMAL(18,4)) as WBTotal from AP aps left join Waybill_positions wp on wp.alccode=aps.alccode where wp.alccode='0001821000001389010' group by aps.fullname, aps.productvcode, aps.alccode, aps.capacity
),
CTE2 as
(
    Select aps.fullname, aps.productvcode, aps.alccode, aps.capacity, Cast(Round(Sum(case when wp.capacity>0 then wp.quantity * wp.capacity/10 else wp.quantity * wp.capacity end),4) as DECIMAL(18,4)) as WBTotal from AP aps left join Waybill_out_positions wp on wp.alccode=aps.alccode where wp.alccode='0001821000001389010' group by aps.fullname, aps.productvcode, aps.alccode, aps.capacity
)
Select CTE1.fullname, CTE1.productvcode, CTE1.alccode, CTE1.capacity, CTE1.WBTotal, CTE2.WBTotal as WBOTOTAL
From  CTE1 
join  CTE2
on CTE1.productvcode = CTE2.productvcode
and CTE1.alccode = CTE2.alccode