C# Linq To DataTable 分組統計 DEMO，
DataTable dt = SQLLayer.Get工作量統計(beginDate, endDate);
var querySum = from t in dt.AsEnumerable()
group t by t.Field<string>("庫房")
into g
select new
{
_庫房 = g.Key,
_品次_入庫 = g.Sum(t => t.Field<decimal>("品次_入庫")),
_品種_入庫 = g.Sum(t => t.Field<decimal>("品種_入庫")),
_金額_入庫 = g.Sum(t => t.Field<decimal>("金額_入庫")),
_品次_出庫 = g.Sum(t => t.Field<decimal>("品次_出庫")),
_品種_出庫 = g.Sum(t => t.Field<decimal>("品種_出庫")),
_金額_出庫 = g.Sum(t => t.Field<decimal>("金額_出庫")),
_品種_現存 = g.Sum(t => t.Field<decimal>("品種_現存")),
_金額_現存 = g.Sum(t => t.Field<decimal>("金額_現存"))
};
DataTable newDt = new DataTable();
DataRow newRow = null;
newDt.Columns.Add("庫房", typeof(string));
newDt.Columns.Add("品次_入庫", typeof(decimal));
newDt.Columns.Add("品種_入庫", typeof(decimal));
newDt.Columns.Add("金額_入庫", typeof(decimal));
newDt.Columns.Add("品次_出庫", typeof(decimal));
newDt.Columns.Add("品種_出庫", typeof(decimal));
newDt.Columns.Add("金額_出庫", typeof(decimal));
newDt.Columns.Add("品種_現存", typeof(decimal));
newDt.Columns.Add("金額_現存", typeof(decimal));
foreach (var query in querySum)
{
newRow = newDt.NewRow();
newRow["庫房"] = query._庫房;
newRow["品次_入庫"] = query._品次_入庫;
newRow["品種_入庫"] = query._品種_入庫;
newRow["金額_入庫"] = query._金額_入庫;
newRow["品次_出庫"] = query._品次_出庫;
newRow["品種_出庫"] = query._品種_出庫;
newRow["金額_出庫"] = query._金額_出庫;
newRow["品種_現存"] = query._品種_現存;
newRow["金額_現存"] = query._金額_現存;
newDt.Rows.Add(newRow);
}
private void btnLinqTest__Click(object sender, EventArgs e)
{
DataTable vtblSeqBase = this.DM.bdsSingle._mpDataTable;
//1. GroupBy 統計
var query =
from q in vtblSeqBase.AsEnumerable()
group q by q.Field<string>("MachineType") into r
select new
{
_qMachType = r.Key,
_qCount = r.Count()
};
using (DataTable vtblCount = new DataTable())
{
DataRow vNewRow = null;
vtblCount.Columns.Add("MachType", typeof(string));
vtblCount.Columns.Add("Count", typeof(int));
foreach (var vq in query)
{
vNewRow = vtblCount.NewRow();
vNewRow["MachType"] = vq._qMachType;
vNewRow["Count"] = vq._qCount;
vtblCount.Rows.Add(vNewRow);
}
//vtblCount.mmViewForRD();
}
//2. Distinct用法 XXXTestOk 2011-07-18
var d = (from DataRow vRow in vtblSeqBase.Rows
select new { _MachType = vRow["MachineType"] }).Distinct();
using (DataTable vtblDistinct = new DataTable())
{
DataRow vNewRow = null;
vtblDistinct.Columns.Add("MachType", typeof(string));
vtblDistinct.Columns.Add("Count", typeof(int));
foreach (var q in d)
{
vNewRow = vtblDistinct.NewRow();
vNewRow["MachType"] = q._MachType;
vtblDistinct.Rows.Add(vNewRow);
}
//vtblDistinct.mmViewForRD();
}
//DataTableExtensions.CopyToDataTable
//3.
List<int> vListNum = new List<int> { 21, 46, 46, 55, 17, 21, 55, 55 };
IEnumerable<int> vDistNum = vListNum.Distinct();
foreach (int n in vDistNum)
{
//MessageBox.Show("不重復的數值 =" + n.ToString());
}
//4. Group By 一個字段
var querySum =
from q in vtblSeqBase.AsEnumerable()
group q by q.Field<string>("MachineType") into g
select new
{
_qMachType = g.Key,
_qSamTotal = g.Sum(q => q.Field<decimal>("SamValue"))
};
using (DataTable vtblSAMTotal = new DataTable())
{
DataRow vNewRow = null;
vtblSAMTotal.Columns.Add("MachType", typeof(string));
vtblSAMTotal.Columns.Add("SAMTotal", typeof(decimal));
foreach (var vq in querySum)
{
vNewRow = vtblSAMTotal.NewRow();
vNewRow["MachType"] = vq._qMachType;
vNewRow["SAMTotal"] = vq._qSamTotal;
vtblSAMTotal.Rows.Add(vNewRow);
}
//vtblSAMTotal.mmViewForRD();
}
//5. Group By 多個字段 GBN = Group By NField
var queryGBN =
from q in vtblSeqBase.AsEnumerable()
group q by new {機器代碼=q.Field<string>("MachineType"), 工序代碼=q.Field<string>("SeqCode") }
into g
select new
{
_qMachType = g.Key,
_qSamTotal = g.Sum(q => q.Field<decimal>("SamValue")),
_qSamAvg = g.Average(q => q.Field<decimal>("SamValue"))
};
using (DataTable vtblSAMTotal = new DataTable())
{
DataRow vNewRow = null;
vtblSAMTotal.Columns.Add("MachType", typeof(string));
vtblSAMTotal.Columns.Add("SeqCode", typeof(string));
vtblSAMTotal.Columns.Add("SAMTotal", typeof(decimal));
vtblSAMTotal.Columns.Add("SAMAvg", typeof(decimal));
foreach (var vq in queryGBN)
{
vNewRow = vtblSAMTotal.NewRow();
vNewRow["MachType"] = vq._qMachType;
vNewRow["SeqCode"] = vq._qMachType;
vNewRow["SAMTotal"] = vq._qSamTotal;
vNewRow["SAMAvg"] = vq._qSamAvg;
vtblSAMTotal.Rows.Add(vNewRow);
}
vtblSAMTotal.mmViewForRD();
}
}
