简单的例子：
SUM(col1 * col2) as X
这就是我似乎得到的：
（col1_row1 + col1_row2）*（col2_row1 + col2_row2）-（基本上首先对列进行“求和”，然后相乘）
我需要的是：
（col1_row1 * col2_row1）+（col1_row2 * col2_row2）
示例：（请注意，这些可以是不同的值）
Name | Rate | Cost
------------------
Bob | .32 | 100
Bob | .32 | 100
Ted | .11 | 78
Ted | .44 | 45
SELECT
Name,
SUM(Rate * Cost) as Value
FROM
Blah
Group BY
Name
预期：Bob = 64-（.32 * 100）+（.32 * 100）
实际：Bob = 128-（.64 * 200）错误
发布我正在处理的实际VIEW：
SELECT
Field.Id as FieldId,
Field.FieldName,
Field.FsaId,
Field.Acres,
Field.PlanYear,
Farm.Id as FarmId,
Grower.Id as GrowerId,
SUM(FertilizerUses.ApplicationRate * Fertilizer.N) as FertN,
SUM(Fertilizer.P2o5 * FertilizerUses.ApplicationRate) as Fert2o5,
SUM(Fertilizer.Mn * FertilizerUses.ApplicationRate) as FertMn,
SUM(Fertilizer.Mg * FertilizerUses.ApplicationRate) as FertMg,
SUM(Fertilizer.K2o * FertilizerUses.ApplicationRate) as FertK2o,
SUM(Fertilizer.Fe * FertilizerUses.ApplicationRate) as FertFe,
SUM(Fertilizer.S * FertilizerUses.ApplicationRate) as FertS,
SUM(Fertilizer.B * FertilizerUses.ApplicationRate) as FertB,
SUM(Fertilizer.Zn * FertilizerUses.ApplicationRate) as FertZn,
SUM(ManureAnalysis.P2o5 * ManureUses.ManureApplied) as ManP2o5,
SUM(ManureAnalysis.OrganicN *ManureUses.ManureApplied) as ManOrganicN,
SUM(ManureAnalysis.Nh4n * ManureUses.ManureApplied) as ManNh4n,
SUM(ManureAnalysis.N * ManureUses.ManureApplied) as ManN,
SUM(ManureAnalysis.K2o * ManureUses.ManureApplied) as ManK2o,
SUM(ManureAnalysis.Dm * ManureUses.ManureApplied) as ManDm
FROM
dbo.Field INNER JOIN
dbo.Farm ON dbo.Farm.Id = Field.FarmId INNER JOIN
dbo.Grower ON dbo.Grower.Id = Farm.GrowerId FULL JOIN
dbo.FertilizerUses ON dbo.Field.Id = dbo.FertilizerUses.FieldId
AND FertilizerUses.PIversion = Field.PIversion
AND FertilizerUses.SnapshotVersionId = Field.SnapshotVersionId FULL JOIN
dbo.Fertilizer ON dbo.FertilizerUses.FertilizerId = dbo.Fertilizer.Id FULL JOIN
dbo.ManureUses ON dbo.Field.Id = dbo.ManureUses.FieldId
AND ManureUses.PIversion = Field.PIversion
AND ManureUses.SnapshotVersionId = Field.SnapshotVersionId FULL JOIN
dbo.ManureSource ON dbo.ManureUses.ManureSourceId = dbo.ManureSource.Id FULL JOIN
dbo.ManureAnalysis ON dbo.ManureUses.ManureAnalysisId = dbo.ManureAnalysis.Id
Group BY
Field.Id,
Field.FieldName,
Field.FsaId,
Field.Acres,
Field.PlanYear,
Farm.Id,
Grower.Id
我在Sql Server 2017中尝试了以下查询：
DECLARE @Temp TABLE (
[Name] varchar(100),
Rate decimal(2,2),
Cost INT
);
INSERT INTO @Temp
SELECT 'Bob',0.32,100 UNION ALL
SELECT 'Bob',0.32,100 UNION ALL
SELECT 'Ted',0.11,78 UNION ALL
SELECT 'Ted',0.44,45
;
SELECT * FROM @Temp;
SELECT
Name,
SUM(Rate*Cost) AS Value
FROM @Temp
GROUP BY Name
它给了我两个结果集：|名称|费率|费用| |-|-|-|| |鲍勃| 0.32 | 100 | |鲍勃| 0.32 | 100 | | Ted | 0.11 | 78 | | Ted | 0.44 | 45 |
并且：||名称|值| |-|-| |鲍勃| 64.00 | | Ted | 28.38 |
这似乎是对的吗？
本文收集自互联网，转载请注明来源。
如有侵权，请联系 [email protected] 删除。
我来说两句