SQL SERVER
数据是直接从表查出来的，我如何实现数量、单价、金额的合计呢？
;with cet1 as
(
select finterid,t3.fname 仓库名称 ,t1.FNumber,t1.FName 产品名称,t1.FModel,t2.FName 单位,t0.FQty,t0.FPrice,t0.FAmount from ICStockBillEntry t0
inner join t_ICItem t1 on t0.FItemID=t1.FItemID
inner join t_MeasureUnit t2 on t0.FUnitID=t2.FItemID
inner join t_Stock t3 on t0.FDCStockID=t3.FItemID )
select FDate,d1.FName,fbillno,cet1.仓库名称,cet1.FNumber,cet1.产品名称,cet1.FModel,cet1.单位,cet1.FQty,cet1.FPrice,cet1.FAmount
from ICStockBill d0
inner join t_Department d1 on d0.FDeptID =d1.FItemID
inner join cet1 on d0.FInterID =cet1.FInterID
------解决思路----------------------
直接用UNION ALL 连接统计语句。
注意字段对应。
;with cet1 as
(
select finterid,t3.fname 仓库名称 ,t1.FNumber,t1.FName 产品名称,
t1.FModel,t2.FName 单位,t0.FQty,t0.FPrice,t0.FAmount
from ICStockBillEntry t0
inner join t_ICItem t1 on t0.FItemID=t1.FItemID
inner join t_MeasureUnit t2 on t0.FUnitID=t2.FItemID
inner join t_Stock t3 on t0.FDCStockID=t3.FItemID
)
select
FDate,d1.FName,fbillno,cet1.仓库名称,
cet1.FNumber,cet1.产品名称,cet1.FModel,cet1.单位,
cet1.FQty,cet1.FPrice,cet1.FAmount
from ICStockBill d0
inner join t_Department d1 on d0.FDeptID =d1.FItemID
inner join cet1 on d0.FInterID =cet1.FInterID
union all
select
'' as FDate, '' as FName, '' as fbillno, '' as 仓库名称,
'' as FNumber, '' as 产品名称, '' as FModel, '' as 单位,
sum(cet1.FQty), sum(cet1.FPrice), sum(cet1.FAmount)
from cet1