通过从 SQL Server 2008 R2 中的另一个表中获取总和来更新表条目

Question

我正在使用SQL Server 2008 R2。我使用以下查询来查看包装中所有产品的价格乘以数量的总和。

select 
    a.nid, a.packagename,
    case
       when sum(b.quantity * c.price) is null 
          then 0 
          else sum(b.quantity * c.price) 
    end 
from 
    tblpackage as a
left join
    tblpackage_service as b on a.nid = b.package_id
left join 
    tblproduct as c on c.nid = b.service_id
group by 
    a.nid, a.packagename

返回以下结果

nid  packagename      (No Column Name)
---------------------------------------
6    Test Package 4    400.00
3    Test Package 5     0.00
5    Test Package 6   1350.00
8    Test Package 7    350.00
7    Test Package 8    400.00
9    Test Package 9    400.00
1    Test Package 1      0.00
2    Test Package 2      0.00
4    Test Package 3   3900.00

现在我使用

update tblpackage 
set tblpackage.amount = case 
                           when sum(b.quantity * c.price) is null 
                              then 0 
                              else sum(b.quantity * c.price) 
                        end 
from tblpackage AS a
left join tblpackage_service as b on a.nid = b.package_id
left join tblproduct as c on c.nid = b.service_id

我收到一个错误：

聚合可能不会出现在 UPDATE 语句的集合列表中。

那么有没有办法使用更新查询来做到这一点。

请帮忙。提前谢谢。

Answer 1

试试这个：

;WITH tblpackage_serviceCTE AS(
        SELECT package_id,SUM(quantity ) as quantity 
        FROM tblpackage_service
        GROUP BY package_id
),tblproductCTE AS(
        SELECT nid,SUM(price) as price
        FROM tblproduct
        GROUP BY nid
)  
update tblpackage 
set tblpackage.amount = CASE WHEN tblpackage_serviceCTE.quantity * tblproductCTE .price) IS NULL 
                             THEN 0 
                             ELSE tblpackage_serviceCTE.quantity * tblproductCTE .price 
                        END 
from tblpackage AS a
left join tblpackage_serviceCTE as b on a.nid = b.package_id
left join tblproductCTE as c on c.nid = b.service_id

注意：先备份:p

Answer 2

也许这会更好。

 ;WITH newtblpackage_service AS(
        SELECT package_id,SUM(quantity*price) as sum1 
        FROM tblpackage_service a left join tblproduct as c on c.nid = a.service_id
        GROUP BY package_id
) 
update tblpackage 
set tblpackage.amount = CASE WHEN sum1 IS NULL 
                             THEN 0 
                             ELSE sum1
                        END 
from tblpackage AS a
left join newtblpackage_service as b on a.nid = b.package_id

因为tblpackage_service可以用来找到总和（看起来如此）。