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

Question

>我有以下简化表：

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

面团材料是一对多的关系，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%   ║ 
╚══════════╩═══════════╝═══════════╝═══════════╝

Answer 1

一个更简洁的版本：

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;

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

Answer 2

您也可以将其包装到 cte 中

with updatestatement as  (SELECT D.Dough
,Material
,PerCharge
,PerCharge/Total as Percentage
FROM 
(Select Dough
,sum(PerCharge) as Total
From [dbo].[Deegregels]
group by Dough
) as D
left join
(SELECT Dough
,Material
,PerCharge
FROM [dbo].[Deegregels]
) as D2
On D.Dough = D2.Dough
)
update b
set perc = a.percentage
from updatestatement a inner join dbo.[Deegregels] b on a.dough = b.dough and a.material = b.material

Answer 3

你应该可以试试这个：

CREATE TABLE #Dough
(
Dough VARCHAR(10) NULL,
PerCharge DECIMAL(10,2) NULL,
[Percentage] DECIMAL(10,2) NULL
)
INSERT INTO #Dough
VALUES
('DG10001',2,NULL),('DG10001',9,NULL),('DG10001',9,NULL),('DG10002',2,NULL)
SELECT *
FROM #Dough
;WITH Total AS
(
SELECT   Dough
,Total = SUM(PerCharge)
FROM #Dough
GROUP BY Dough
)
UPDATE D1
SET [Percentage] = D1.PerCharge/T1.Total
FROM #Dough D1
JOIN Total  T1 ON D1.Dough = T1.Dough