请教各位大仙,如何通过linq查询将 左边的DataTable 合并成右侧 table其中每行中列S_XM,I_NL相同的分组 ,将I_SL相加,每行中S_BH按照逗号分隔显示成一行如下图S_XM I_NL S_BH I_SL S_XM I_NL S_BH I_SL
张三 23 H001 1 张三 23 H001,H002 3
张三 23 H002 2 李四 22 H210,H220,H230 5
李四 22 H210 2 王五 21 H992 1
李四 22 H220 2
李四 22 H230 1
王五 21 H992 1
张三 23 H001 1 张三 23 H001,H002 3
张三 23 H002 2 李四 22 H210,H220,H230 5
李四 22 H210 2 王五 21 H992 1
李四 22 H220 2
李四 22 H230 1
王五 21 H992 1
join t2 in tb2
on t1.S_XM equals t2.S_XM
into tmps
from tmp in tmps.DefaultIfEmpty()
select new {Left=t1,Right=tmp};
{
DataTable mytable=new DataTable();
mytable.Columns.Add("str",typeof(string));
mytable.Columns.Add("in",typeof(int));
mytable.Columns.Add("str2",typeof(string));
mytable.Columns.Add("in2",typeof(int)); for(int i=0;i<4;i++)
{
DataRow myrow=mytable.NewRow();
myrow["str"]="AAAA";
myrow["in"]=10;
myrow["str2"]="row"+i.ToString();
myrow["in2"]=i;
mytable.Rows.Add(myrow);
} DataTable my = fun(mytable); foreach (var a in my.AsEnumerable())
{
foreach (var b in a.ItemArray)
{
Console.Write("{0} ", b.ToString());
}
Console.WriteLine();
}
}
public static DataTable fun(DataTable mytable)
{
for (int i = 0; i < mytable.Rows.Count; i++)
{
for (int j = i + 1; j < mytable.Rows.Count; j++)
{
if (mytable.Rows[i]["str"].ToString() == mytable.Rows[j]["str"].ToString() && mytable.Rows[i]["in"].ToString() == mytable.Rows[j]["in"].ToString())
{
mytable.Rows[i]["str2"] = mytable.Rows[i]["str2"].ToString() + mytable.Rows[j]["str2"].ToString();
mytable.Rows[i]["in2"] = Convert.ToInt32(mytable.Rows[i]["in2"]) + Convert.ToInt32(mytable.Rows[j]["in2"]);
mytable.Rows[j].Delete();
j--;
}
}
}
return mytable;
}这个是自己写的的例子
var query=yourDt.AsEnumerable().GroupBy(t=>new{S_XM=t.Field<string>("S_XM"),I_NL=t.Field<int>("I_NL")})
.Select(g=>new
{
S_XM=g.Key.S_XM,
I_NL=g.Key.I_NL,
S_BH=string.Join(",",g.Select(x=>x.Field<string>("S_BH")).ToArray()),
I_SL=g.Sum(x=>x.Field<int>("I_SL")
});
into tmp
select new {S_XM=tmp.Key.S_XM, I_NL=tmp.Key.I_NL,I_SL=tmp.Max(t=>{return t.I_SL;},S_BH=string.Join(",",tmp.Select(t=>{return t.S_BH;}).ToArray()) }