时间戳

首页 > 解决方案 > 更新表以包括计算的百分比

sql - 更新表以包括计算的百分比

问题描述

我有以下简化表：

╔══════════╦═══════════╗═══════════╗═══════════╗
║ Dough    ║ Material  ║ PerCharge ║ Perc      ║
╠══════════╬═══════════╣═══════════╣═══════════╣
║ DG10001  ║ GR002152  ║ 2,00      ║           ║ 
║ DG10001  ║ GR000133  ║ 9,00      ║           ║ 
║ DG10001  ║ GR000133  ║ 9,00      ║           ║ 
║ DG10002  ║ GR002152  ║ 2,50      ║           ║ 
╚══════════╩═══════════╝═══════════╝═══════════╝

Dough-Material 是一对多的关系，PerCharge 是每个容器使用的原材料重量。

我想根据每个独特面团使用的总数计算每个单独行的百分比。

我使用以下方法计算百分比

SELECT D.Dough
       ,Material
       ,PerCharge
       ,PerCharge/Total as Percentage
  FROM 
(Select Dough
       ,sum(PerCharge) as Total
From [PP_Materials].[dbo].[Deegregels]

group by Dough
) as D

left join

(SELECT Dough
      ,Material
     ,PerCharge
FROM [PP_Materials].[dbo].[Deegregels]
) as D2
On D.Dough = D2.Dough

order by D.Dough

现在我只需要更新原始表以包含这个计算的百分比，但我不知道如何制定更新语句。

感谢您提供任何帮助。

编辑：简化示例的结果

╔══════════╦═══════════╗═══════════╗═══════════╗
║ Dough    ║ Material  ║ PerCharge ║ Perc      ║
╠══════════╬═══════════╣═══════════╣═══════════╣
║ DG10001  ║ GR002152  ║ 2,00      ║     10%   ║ 
║ DG10001  ║ GR000133  ║ 9,00      ║     45%   ║ 
║ DG10001  ║ GR000133  ║ 9,00      ║     45%   ║ 
║ DG10002  ║ GR002152  ║ 2,50      ║     10%   ║ 
╚══════════╩═══════════╝═══════════╝═══════════╝

标签： sqlsql-serversql-update

解决方案

一个更简洁的版本：

USE Sandbox;
GO
--Create sample table and data
CREATE TABLE #Dough (Dough varchar(10),
                     Material varchar(10),
                     PerCharge decimal(5,2),
                     Perc decimal(3,2));

INSERT INTO #Dough (Dough,
                    Material,
                    PerCharge)
VALUES ('DG10001','GR002152',2.00), 
       ('DG10001','GR000133',9.00), 
       ('DG10001','GR000133',9.00), 
       ('DG10002','GR002152',2.50);
GO
--The solution

WITH CTE AS(
    SELECT D.Dough, D.Material, D.PerCharge, Perc,
           PerCharge / SUM(PerCharge) OVER (PARTITION BY Dough) AS NewPerc
    FROM #Dough D)
UPDATE CTE
SET Perc = NewPerc
GO
--Check the data
SELECT *
FROM #Dough
GO
--Clean up
DROP TABLE #Dough;

当您可以使用窗口函数时，无需扫描表两次。

推荐阅读