Linq to sql计算查询中一组项的百分比

本文关键字：一组 百分比 sql to 计算 查询 Linq | 更新日期: 2023-09-27 18:10:11

Linq to SQL新手和SQL新手。第一个帖子，所以请温柔。
我有一些类似于下面的表，我正在查询一个dataGridView基于c#中的日期范围。

HeatNumber chargennumber权重DOB

1            1              500     8/26/15
1            2              3500    8/26/15
1            3              2200    8/26/15
2            1              2000    8/27/15
2            2              1100    8/27/15

var query = from SU in dct.GetTable<ScrapInCharge>()
                        where ((SU.DOB >= dateTimePicker2.Value.Date) &&
                               (SU.DOB <= dateTimePicker1.Value.Date))
                        orderby SU.HeatNumber descending
                        select SU;
            scrapInChargeBindingSource.DataSource = query;

我需要添加一列，显示每个chargennumber所占的总HeatNumber权重的百分比。我确实知道如何通过HeatNumber获得每个热量的总重量。

var TotalHeatWgt = from a in dct.ScrapInCharges
                           where ((a.DOB >= dateTimePicker2.Value.Date) &&
                               (a.DOB <= dateTimePicker1.Value.Date))
                           group a.Weight by a.HeatNumber
                               into b
                               select new { HeatNumber = b.Key, TotalWgt = b.Sum() };

我目前卡住了如何将其合并到单个c#查询中，在权重列之后插入%的Heat列。这是可以做到的，或者我需要添加一个未绑定的列到dataGridView %的热量和迭代使用我的TotalHeatWgt查询的返回值的行?

Linq to sql计算查询中一组项的百分比

简单尝试:

var results = data.Select(d => new {
    d.HeatNumber,
    d.ChargeNumber,
    d.Weight,
    Percent = 100.0 * d.Weight / data.Where(dd => dd.HeatNumber == d.HeatNumber).Sum(dd => dd.Weight)
});

或

from row in data
select new { 
    row.HeatNumber,
    row.ChargeNumber,
    row.Weight,
    Percent = 100.0 * row.Weight / (from innerRow in data
                                        where innerRow.HeatNumber == row.HeatNumber
                                        select innerRow.Weight
                                    ).Sum()
};

下面这个会更高效(至少在内存中，我不确定它在SQL中是否会更好):

var results = data.GroupBy (d => d.HeatNumber)
    .SelectMany (grp => grp.Select(row => new {
        row.HeatNumber,
        row.ChargeNumber,
        row.Weight,
        Percent = 100.0 * row.Weight / grp.Sum(dd => dd.Weight)
    })
);

和查询语法:

from row in data 
group row by row.HeatNumber into grp
from innerRow in grp
select new { 
    innerRow.HeatNumber,
    innerRow.ChargeNumber,
    innerRow.Weight,
    Percent = 100.0 * innerRow.Weight / grp.Sum(dd => dd.Weight)
};

它们都输出这样的结果:

HeatNumber    ChargeNumber     Weight  Percent 
1             1                500     8.06451612903226 
1             2                3500    56.4516129032258 
1             3                2200    35.4838709677419 
2             1                2000    64.5161290322581 
2             2                1100    35.4838709677419

这就是你想要的吗?为了简单起见，我省略了按日期过滤，因为实际问题出现在

之后。
相关文章：