C# Linq To DataTable 分组统计

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

 

转载于:https://www.cnblogs.com/junjie94wan/archive/2012/09/11/2679806.html