查找多个相似点 [英] Looking up through multiple points of similarity

问题描述

我有一个 GroupBy 语句，我想添加到。我有两张表，我从这样拉：

I have a GroupBy statement that I would like to add to. I have two tables that I am pulling from like so:

MU_Reports表

MU_Reports Table

Date      | Shift | Machine | MU
1/12/2016 |   1   |   12    | 44%
1/12/2016 |   2   |   12    | 34%
1/12/2016 |   2   |   12    | 34%
1/12/2016 |   3   |   12    | 54%
1/12/2016 |   3   |   13    | 24%

DownTime_Reports表

DownTime_Reports Table

Date      | Shift | Machine | DT Code  |  DT_Hours
1/12/2016 |   1   |   12    | No Work  |    3
1/12/2016 |   2   |   12    | No Resin |    2
1/12/2016 |   2   |   12    | No Op.   |    4
1/12/2016 |   3   |   12    | No Work  |    1
1/12/2016 |   3   |   13    | No Work  |    5
1/12/2016 |   3   |   12    | No Work  |    5

输出示例：

Date      | Shift | Machine | MU   |  No_Work_Hours
1/12/2016 |   1   |   12    | 44%  |     3
1/12/2016 |   2   |   12    | 68%  |     0 
1/12/2016 |   3   |   12    | 54%  |     6
1/12/2016 |   3   |   13    | 24%  |     5

我目前的 GroupBy 一起工作（无论日期或机器）：

my current GroupBy statement adds all No Work together in one shift (no matter date or machine):

using (var db = new ProductionContext())
{
    var query2 = from b in db.MU_Reports
                 join downtime in db.Downtime_Reports on new { b.Shift, b.Date, b.Machine_Number } equals new { downtime.Shift, downtime.Date, downtime.Machine_Number }
                 where downtime.DT_Code.Equals("No Work")
                 group downtime by new { b.Date, b.Shift, b.Machine, b.MU } into g
                 select new
                 {
                       Date = g.Key.Date,
                       Shift = g.Key.Shift,
                       Machine = g.Key.Machine,
                       MU = g.Key.MU,
                       NWTotal = g.Sum(x => x.No_Work_Hours)
                 };
}

正如您在示例输出中可以看到的，我要做的是添加所有的无工作在具体的日期，班次和机器的同时。所以换句话说，我必须通过Down_Time表来查看正确的日期，然后查看这些日期才能得到正确的转换，然后获取正确的机器，然后获得正确的DT原因获取小时数。

As you can see in my example output what I want to do is to add all the No Work in the specific date, shift, and machine at the same time. So in other words I have to look through the Down_Time table to get the correct date then look through those dates to get the right shift then to get the correct machine to then get the right DT reason to get the amount of hours.

编辑：我更新了代码，因为我尝试进行多个连接，但是现在我根本不输出任何内容。

I updated the code because I attempted to do multiple joins, however, now I do not output anything at all.

推荐答案

有几种方法来产生所需的输出。所有他们需要首先将 MU_Reports 表格分组为 Date ， Shift 和机器。那么您可以将组合分组的子查询 DownTime_Reports 表由日期， Shift 和机器并执行聚合：

There are several ways to produce the desired output. All they require to first group the MU_Reports table by Date, Shift and Machine. Then you can for instance do group join the grouped subquery to the DownTime_Reports table by Date, Shift and Machine and perform the aggregates:

var query =
    from rg in db.MU_Reports.GroupBy(r => new { r.Date, r.Shift, r.Machine })
    join dt in db.Downtime_Reports
    on new { rg.Key.Date, rg.Key.Shift, rg.Key.Machine }
    equals new { dt.Date, dt.Shift, dt.Machine }
    into dtGroup
    select new
    {
        rg.Key.Date, rg.Key.Shift, rg.Key.Machine,
        MU = rg.Sum(r => r.MU),
        NWTotal = dtGroup.Where(dt => dt.DT_Code == "No Work").Sum(dt => dt.DT_Hours)
    };

这篇关于查找多个相似点的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！