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();
            }
        }