将两张表组合成一张 [英] Combining two tables into one
问题描述
示例
模式1
ID，
食物，
书，
米，
洞穴
模式2
ID，
地毯，
字符串，
运行
模式3
ID，
食物，
书，
米，
洞穴，
地毯，
字符串，
运行
使用此命令填充Schema3表格
Sql命令：
选择* Schema1 [除ID]和Schema2的所有字段[exceptID] Inner Join
Schema2 ON Schema1.ID = Schema2.ID
其中ID = {动态定义的变量'X'}
请原谅缺乏正确的语法。这里的主要问题是使用select语句查询数据集，并填写一个具有结果的表。我并不完全连接到我的数据库，因为我已经在本地填写了一个数据集。
------编辑------
我真的只需要一种从两个查询中创建数据行数组的方法
您可以使用这种扩展方法，这是最近从头开始写的另一个问题。它可以通过公共密钥合并多个表。如果没有指定键，它将只使用默认的 DataTable。合并 方法：
public static DataTable MergeAll（此IList< DataTable> String primaryKeyColumn）
{
if（！tables.Any（））
throw new ArgumentException（Tables must not be empty，tables）;
if（primaryKeyColumn！= null）
foreach（表中的DataTable t）
if（！t.Columns.Contains（primaryKeyColumn））
throw new ArgumentException（所有表必须具有指定的主键列+ primaryKeyColumn，primaryKeyColumn）;
if（tables.Count == 1）
返回表[0];
DataTable table = new DataTable（TblUnion）;
table.BeginLoadData（）; //加载数据时关闭通知，索引维护和约束
foreach（表中的DataTable t）
{
table.Merge（t）; //与table.Merge（t，false，MissingSchemaAction.Add）相同;
}
table.EndLoadData（）;
if（primaryKeyColumn！= null）
{
//因为我们可能没有定义真正的主键，所以行现在可能会有重复的字段
// so现在我们要加入这些行...
var pkGroups = table.AsEnumerable（）
.GroupBy（r => r [primaryKeyColumn]）;
var dupGroups = pkGroups.Where（g => g.Count（）> 1）;
foreach（var grpDup in dupGroups）
{
//使用第一行并修改它
DataRow firstRow = grpDup.First（）;
foreach（table.Columns中的DataColumn c）
{
if（firstRow.IsNull（c））
{
DataRow firstNotNullRow = grpDup.Skip（1）。 FirstOrDefault（r =>！r.IsNull（c））;
if（firstNotNullRow！= null）
firstRow [c] = firstNotNullRow [c];
}
}
//除去第一行
var rowsToRemove = grpDup.Skip（1）;
foreach（DataRow rowToRemove in rowsToRemove）
table.Rows.Remove（rowToRemove）;
}
}
返回表;
}
您可以这样调用：
var tables = new [] {Schema1，Schema2};
DataTable Schema3 = tables.MergeAll（ID）;
修改：如果您不需要新的 DataTable 与合并的模式，你也可以使用 Linq-To-DataSet （现在是VB.NET）：
Dim schema3 =从r1在schema1
加入r2在schema2中r1.Field（Of Int32）（ID）等于r2.Field（Of Int32）（ID）
选择New $ {
.ID = r1.Field（Of Int32）（ID），
.Food = r1.Field（Of String）（Food），
.Book = r1.Field（Of String）（Book），
.Rice = r1.Field（Of String）（Rice），
.Cave = r1.Field（Of String）（Cave ），
.Carpet = r2.Field（Of String）（Carpet），
.Strings = r2.Field（Of String）（Strings），
.Run = r2.Field（Of String）（Run）
}
I need to merge two data schema into one. I have Schema1 and Schema2. I need to join these two into Schema3. Furthermore, I have a Select statement which queries a data set but I can't figure out how to use the select statement on the data set containing both tables (both Schema1 and Schema2) and combine them into the new table schema3 which is a table in the same data set comprised of both table's fields.
Example
Schema 1 ID, Food, Book, Rice, Cave
Schema 2 ID , Carpet, Strings, Run
Schema 3 ID, Food, Book, Rice, Cave, Carpet, Strings, Run
Fill the Schema3 Table with this command
Sql Command:
Select * Schema1 [except ID] and all fields from Schema2 [exceptID] Inner Join
Schema2 ON Schema1.ID = Schema2.ID
Where ID = {dynamically defined variable 'X'}
Please excuse the lack of proper syntax. The main issue here again is querying a dataset with the select statement and filling up a table with the results. Im not exactly connecting to my DB because I already filled a dataset locally.
------Edit ------ I really just need a way to create an array of data rows from a query of two tables.
You could use this extension method here which i've written from scratch recently for another question. It enables to merge multiple tables by a common key. If no key is specified it will just use the default DataTable.Merge method:
public static DataTable MergeAll(this IList<DataTable> tables, String primaryKeyColumn)
{
if (!tables.Any())
throw new ArgumentException("Tables must not be empty", "tables");
if(primaryKeyColumn != null)
foreach(DataTable t in tables)
if(!t.Columns.Contains(primaryKeyColumn))
throw new ArgumentException("All tables must have the specified primarykey column " + primaryKeyColumn, "primaryKeyColumn");
if(tables.Count == 1)
return tables[0];
DataTable table = new DataTable("TblUnion");
table.BeginLoadData(); // Turns off notifications, index maintenance, and constraints while loading data
foreach (DataTable t in tables)
{
table.Merge(t); // same as table.Merge(t, false, MissingSchemaAction.Add);
}
table.EndLoadData();
if (primaryKeyColumn != null)
{
// since we might have no real primary keys defined, the rows now might have repeating fields
// so now we're going to "join" these rows ...
var pkGroups = table.AsEnumerable()
.GroupBy(r => r[primaryKeyColumn]);
var dupGroups = pkGroups.Where(g => g.Count() > 1);
foreach (var grpDup in dupGroups)
{
// use first row and modify it
DataRow firstRow = grpDup.First();
foreach (DataColumn c in table.Columns)
{
if (firstRow.IsNull(c))
{
DataRow firstNotNullRow = grpDup.Skip(1).FirstOrDefault(r => !r.IsNull(c));
if (firstNotNullRow != null)
firstRow[c] = firstNotNullRow[c];
}
}
// remove all but first row
var rowsToRemove = grpDup.Skip(1);
foreach(DataRow rowToRemove in rowsToRemove)
table.Rows.Remove(rowToRemove);
}
}
return table;
}
You can call it in this way:
var tables= new[] { Schema1, Schema2};
DataTable Schema3 = tables.MergeAll("ID");
Edit: If you don't need a new DataTable with the merged schema you could also use Linq-To-DataSet (now VB.NET):
Dim schema3 = From r1 In schema1
Join r2 In schema2 On r1.Field(Of Int32)("ID") Equals r2.Field(Of Int32)("ID")
Select New With {
.ID = r1.Field(Of Int32)("ID"),
.Food = r1.Field(Of String)("Food"),
.Book = r1.Field(Of String)("Book"),
.Rice = r1.Field(Of String)("Rice"),
.Cave = r1.Field(Of String)("Cave"),
.Carpet = r2.Field(Of String)("Carpet"),
.Strings = r2.Field(Of String)("Strings"),
.Run = r2.Field(Of String)("Run")
}
这篇关于将两张表组合成一张的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
