如何做添加在Sql Server中使用动态生成字段?
问题描述:
我有名为“购物车”的表。字段是:如何做添加在Sql Server中使用动态生成字段?
CartId
ClientId
ProductId
Qty
现在我加入这个表ProductMaster
得到ProductName
和ProuctPrice
select cc.CartId,cc.ProductId,pm.ProductName,cc.Qty,
(pm.ProductPrice * cc.Qty) As SubTotal
from ClientCart as cc
inner join ProductMaster as pm on cc.ProductId = pm.ProductId
现在我想生成GrandTotal
使用动态字段SubTotal
。我怎样才能做到这一点?
答
with CTE as
(
select cc.CartId,cc.ProductId,pm.ProductName,cc.Qty,
(pm.ProductPrice * cc.Qty) As SubTotal
from ClientCart as cc
inner join ProductMaster as pm on cc.ProductId = pm.ProductId
where cc.ClientId = @id
)
select *
from CTE
union
select null,null, null,null, sum(SubTotal) -- this is a grand total of all sub totals
from CTE
这不是正确的汇总方法,但也不是你的所以这将很好地工作
答
试试这个:
select subtable.CartId, sum(subtable.SubTotal)
from (
select cc.CartId,cc.ProductId,pm.ProductName,cc.Qty,
(pm.ProductPrice * cc.Qty) As SubTotal
from ClientCart as cc
inner join ProductMaster as pm on cc.ProductId = pm.ProductId
where cc.ClientId = @id) subtable
group by subtable.CartId;
去掉无关的MySQL标签 – Jens
添加一些示例表数据和预期的结果。 (作为格式文本。) – jarlh