结合具体条件的数据 [英] Combine datatables with specific conditions
问题描述
UPDATED：忘了提及，表可能包含多种类型的项目代码
UPDATED: Forgot to mention, the table may contain more than one type of itemcode
我有一个datatable问题。共有14个项目与相同的项目代码。现在有来自不同来源的2张桌子。一个按项目编号分组，总结数量，当count等于10时，转到下一行，每行包含特定信息，如装运和备注。另一个表包含更多的详细信息。
I've got a problem for datatable. There're total 14 items with same itemcode. And now there're 2 tables come from different source. One is grouped by itemcode and sum up the quantity, when count equals to 10, go to next row, and each row contains specific information such as shipment and remarks. Another table contains more detail information.
Source1：分组表
Source1: Grouped table
ItemCode|TotalQty|Shipment|Remarks|Line
=========================================
ITEM01 | 1000 | S001 | R001 | 1 <==10 items here
ITEM01 | 400 | S002 | R002 | 2 <==4 items here
Source2：Detail table（14 items& rows） p>
Source2: Detail table (14 items & rows)
RefNo|ItemCode|Quantity|Weight|From
=======================================
R001 | ITEM01 | 100 | 50 | US
R002 | ITEM01 | 100 | 50 | US
R003 | ITEM01 | 100 | 50 | US
. | . | . | . | .
. | . | . | . | .
R013 | ITEM01 | 100 | 50 | US
R014 | ITEM01 | 100 | 50 | US
我想结合source1和source2得到如下结果
I would like to combine source1 and source2 and get the result as below
Shipment|Line|Remarks|ItemCode|TotalQty|RefNo|Quantity|Weight|From
===================================================================
S001 | 1 | R001 | ITEM01 | 1000 | R001| 100 | 50 | US \\1
S001 | 1 | R001 | ITEM01 | 1000 | R002| 100 | 50 | US \\2
S001 | 1 | R001 | ITEM01 | 1000 | R003| 100 | 50 | US \\3
S001 | 1 | R001 | ITEM01 | 1000 | R004| 100 | 50 | US \\4
S001 | 1 | R001 | ITEM01 | 1000 | R005| 100 | 50 | US \\5
S001 | 1 | R001 | ITEM01 | 1000 | R006| 100 | 50 | US \\6
S001 | 1 | R001 | ITEM01 | 1000 | R007| 100 | 50 | US \\7
S001 | 1 | R001 | ITEM01 | 1000 | R008| 100 | 50 | US \\8
S001 | 1 | R001 | ITEM01 | 1000 | R009| 100 | 50 | US \\9
S001 | 1 | R001 | ITEM01 | 1000 | R010| 100 | 50 | US \\10
S002 | 2 | R002 | ITEM01 | 400 | R011| 100 | 50 | US \\11
S002 | 2 | R002 | ITEM01 | 400 | R012| 100 | 50 | US \\12
S002 | 2 | R002 | ITEM01 | 400 | R013| 100 | 50 | US \\13
S002 | 2 | R002 | ITEM01 | 400 | R014| 100 | 50 | US \\14
有没有办法（Linq或循环）获得上述结果？感谢您的帮助！
Is there any way (Linq or looping) to get the above result? Thanks for your help!
推荐答案
可以使用LINQ（我假设你在后，因为你已经包括 LINQ 标签），但不能在我认为 时尚。
It can be done with LINQ (which I assume you're after, since you've included the LINQ tag), but not in what I would consider a nice fashion.
给定两个 DataTable 对象与上述格式和数据，命名为 分组 和 细节 ，这里是一个LINQ表达式，将按照你想要的方式将数据拼接在一起：
Given two DataTable objects with the above formats and data, named grouped and detail, here's a LINQ expression that will stitch the data together in the way you want:
IEnumerable<object[]> qry =
(
from DataRow rDetail in detail.Rows
let dgrp = detail.Rows.IndexOf(rDetail) / 10
join DataRow rGroup in grouped.Rows
on dgrp equals grouped.Rows.IndexOf(rGroup)
orderby rDetail["RefNo"]
select new object[] {
rGroup["Shipment"], rGroup["Line"], rGroup["Remarks"], rGroup["ItemCode"], rGroup["TotalQty"],
rDetail["RefNo"], rDetail["Quantity"], rDetail["Weight"], rDetail["From"]
}
);
现在您需要另一个 DataTable 结果为：
Now you need another DataTable to pump those results into:
DataTable res = new DataTable();
res.Columns.Add("Shipment", typeof(string));
res.Columns.Add("Line", typeof(Int32));
res.Columns.Add("Remarks", typeof(string));
res.Columns.Add("ItemCode", typeof(string));
res.Columns.Add("TotalQty", typeof(Int32));
res.Columns.Add("RefNo", typeof(string));
res.Columns.Add("Quantity", typeof(Int32));
res.Columns.Add("Weight", typeof(Int32));
res.Columns.Add("From", typeof(string));
最后，填写 res LINQ查询的结果：
And finally, fill the res table with the results of the LINQ query:
foreach (object[] rowdata in qry)
res.Rows.Add(rowdata);
以上代码适用于此特定数据集，但我不能再向你保证。它严重依赖于源表中的行顺序，并且因为我使用 DataTable.Rows.IndexOf 来获取订单，这很可能是非常慢的大量数据集。
The code above works for this specific set of data, but I can't promise you anything more than that. It is heavily dependent on the row order in the source tables, and because I'm using DataTable.Rows.IndexOf to get the order it's quite possible that this will be terribly slow on large collections of data.
但是，您已经使用了 DataTable 而不是正确输入的集合，所以所有的投注都是在性能和代码合理的赌注。
But then you're already using DataTables instead of a properly typed collection, so all bets are off in the performance- and code-sanity stakes anyway.
这是一个我建议使用LINQ 不的情况执行任务。 IMHO将更好地做为迭代循环而不是查询。你不会在迭代版本上获得太多的改进（如果有的话），你会失去很多的清晰度，你可以在使用它之前，尽可能多地开始设置东西。
This is one case where I'd suggest not using LINQ to perform the task. IMHO this would be better done as an iterative loop rather than a query. You don't get much (if any) improvement over the iterative version, you lose a lot of clarity, and you have all sorts of fun getting things set up just right before you can use it.
由于我不能单独离开，这里是一个完整的（长，很多代码）解决方案，使用LINQ，类的组合来保存正在处理的数据和迭代生成表：
And because I can't leave well-enough alone, here's a full (long, lots of code) solution using a combination of LINQ, classes to hold the data being processed and iteration to generate the table:
public DataTable MergeShippingData(DataTable groupTable, DataTable detailTable)
{
// convert group table to array of GroupEntry objects
var groupList =
(
from DataRow grouprow in groupTable.Rows
let ent = GroupEntry.FromRow(grouprow)
where ent != null
select ent
).ToArray();
// convert detail table to sequence of DetailEntry objects
var detailSeq =
from DataRow detailrow in detailTable.Rows
let ent = DetailEntry.FromRow(detailrow)
where ent != null
select ent;
// Create output DataTable
DataTable output = CreateOutputTable();
// Process all detail lines into shippings
foreach (var detail in detailSeq)
{
// Find available shipping group for the item code with enough remaining capacity
var grp = groupList.First (g => g.ItemCode == detail.ItemCode && g.Remainder >= detail.Quantity);
if (grp == null)
throw new Exception("No available shipping found for detail item...");
// update remaining space in shipping group
grp.Remainder -= detail.Quantity;
// add data to output table
output.Rows.Add(new object[] {
grp.Shipment, grp.Line, grp.Remarks, grp.ItemCode, grp.TotalQty,
detail.RefNo, detail.Quantity, detail.Weight, detail.From
});
}
return output;
}
// Class to hold the shipping groups while processing
public class GroupEntry
{
// fields from source DataTable
public string ItemCode;
public int TotalQty;
public string Shipment;
public string Remarks;
public int Line;
// process variable, holds remaining quantity value
public int Remainder;
// Convert DataRow into GroupEntry
public static GroupEntry FromRow(DataRow r)
{
try
{
return new GroupEntry
{
ItemCode = r.Field<string>(0),
TotalQty = r.Field<int>(1),
Shipment = r.Field<string>(2),
Remarks = r.Field<string>(3),
Line = r.Field<int>(4),
Remainder = r.Field<int>(1)
};
}
catch { }
return null;
}
}
// Class to hold shipping Detail records during processing
public class DetailEntry
{
public string RefNo;
public string ItemCode;
public int Quantity;
public int Weight;
public string From;
// Convert DataRow into DetailEntry
public static DetailEntry FromRow(DataRow r)
{
try
{
return new DetailEntry
{
RefNo = r.Field<string>(0),
ItemCode = r.Field<string>(1),
Quantity = r.Field<int>(2),
Weight = r.Field<int>(3),
From = r.Field<string>(4)
};
}
catch { }
return null;
}
}
// Create output DataTable
public DataTable CreateOutputTable()
{
DataTable res = new DataTable();
res.Columns.Add("Shipment", typeof(string));
res.Columns.Add("Line", typeof(Int32));
res.Columns.Add("Remarks", typeof(string));
res.Columns.Add("ItemCode", typeof(string));
res.Columns.Add("TotalQty", typeof(Int32));
res.Columns.Add("RefNo", typeof(string));
res.Columns.Add("Quantity", typeof(Int32));
res.Columns.Add("Weight", typeof(Int32));
res.Columns.Add("From", typeof(string));
return res;
}
添加一些错误处理，你很好去。
Add some error handling and you're good to go.
这篇关于结合具体条件的数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
