c#datatable分组groupby
DataTable dt = new DataTable();
dt.Columns.Add("A");
dt.Columns.Add("B");
dt.Columns.Add("C");
dt.Rows.Add("a1", "b1", "c1");
dt.Rows.Add("a1", "b2", "c2");
dt.Rows.Add("a2", "b3", "c3");
dt.Rows.Add("a3", "b4", "c4");
dt.Rows.Add("a1", "b5", "c5");
dt.Rows.Add("a2", "b6", "c6");
IEnumerable<IGrouping<string, DataRow>> result = dt.Rows.Cast<DataRow>().GroupBy<DataRow, string>(dr => dr["A"].ToString());//按A分组foreach (IGrouping<string, DataRow> ig in result)
{
Console.WriteLine("当A="+ig.Key + "：");
Console.WriteLine("A".PadRight(10) + "B".PadRight(10) + "C".PadRight(10));
Console.WriteLine("=====".PadRight(10) + "=====".PadRight(10) + "=====".PadRight(10));
foreach (var dr in ig)
Console.WriteLine(dr["A"].ToString().PadRight(10) + dr["B"].ToString().PadRight(10) + dr["C"].ToString().PadRight(10));
}
另外微软知识库⾥⾯有个DataSetHelper ，可以对DataSet中的DataTable进⾏Distinct、Group by、Join和Create
using System;
using System.Collections;
using System.Data;
namespace Common
{
适合小朋友玩的游戏/**//**//**//// <summary>
/// DataSet助⼿
/// </summary>
public class DataSetHelper
{
private class FieldInfo
{
public string RelationName;
public string FieldName;
public string FieldAlias;
public string Aggregate;
}
private DataSet ds;
private ArrayList m_FieldInfo;
private string m_FieldList;
private ArrayList GroupByFieldInfo;
private string GroupByFieldList;
public DataSet DataSet
{
get { return ds; }
}
ConstructionConstruction#region Construction
public DataSetHelper()
{
ds = null;
}
public DataSetHelper(ref DataSet dataSet)
{
ds = dataSet;
}
#endregion
Private MethodsPrivate Methods#region Private Methods
private bool ColumnEqual(object objectA, object objectB)
{
if ( objectA == DBNull.Value && objectB == DBNull.Value )
{
return true;
}
if ( objectA == DBNull.Value || objectB == DBNull.Value )
{
return fal;
}
return ( objectA.Equals( objectB ) );
}
private bool RowEqual(DataRow rowA, DataRow rowB, DataColumnCollection columns)
{
bool result = true;
for ( int i = 0; i < columns.Count; i++ )
{
result &= ColumnEqual( rowA[ columns[ i ].ColumnName ], rowB[ columns[ i ].ColumnName ] ); }
return result;
}
private void ParFieldList(string fieldList, bool allowRelation)
{
if ( m_FieldList == fieldList )
{
return;
}
m_FieldInfo = new ArrayList();
m_FieldList = fieldList;
FieldInfo Field;
string[] FieldParts;
string[] Fields = fieldList.Split( ',' );
for ( int i = 0; i <= Fields.Length - 1; i++ )
{
Field = new FieldInfo();
FieldParts = Fields[ i ].Trim().Split( ' ' );
switch ( FieldParts.Length )
{
少年心事当拏云ca 1:
//to be t at the end of the loop
break;
ca 2:
Field.FieldAlias = FieldParts[ 1 ];
break;
default:
return;
}
FieldParts = FieldParts[ 0 ].Split( '.' );
switch ( FieldParts.Length )
{
ca 1:
Field.FieldName = FieldParts[ 0 ];
break;
ca 2:
if ( allowRelation == fal )
螃蟹煮多久可以吃
{
return;
}
Field.RelationName = FieldParts[ 0 ].Trim();
Field.FieldName = FieldParts[ 1 ].Trim();
break;
default:
return;
}
if ( Field.FieldAlias == null )
{
Field.FieldAlias = Field.FieldName;
}
m_FieldInfo.Add( Field );
}
}
private DataTable CreateTable(string tableName, DataTable sourceTable, string fieldList)
{
DataTable dt;
if ( fieldList.Trim() == "" )
{
dt = sourceTable.Clone();
dt.TableName = tableName;
}
el
{什么鱼不能吃
dt = new DataTable( tableName );
ParFieldList( fieldList, fal );
DataColumn dc;
foreach ( FieldInfo Field in m_FieldInfo )
{
dc = sourceTable.Columns[ Field.FieldName ];
DataColumn column = new DataColumn();
column.ColumnName = Field.FieldAlias;
column.DataType = dc.DataType;
column.MaxLength = dc.MaxLength;
column.Expression = dc.Expression;
dt.Columns.Add( column );
}
}
if ( ds != null )
{
ds.Tables.Add( dt );
}
return dt;
}
private void InrtInto(DataTable destTable, DataTable sourceTable, string fieldList, string rowFilter, string sort)
{
ParFieldList( fieldList, fal );
DataRow[] rows = sourceTable.Select( rowFilter, sort );
DataRow destRow;
foreach ( DataRow sourceRow in rows )
{
隐形的翅膀电影destRow = destTable.NewRow();
if ( fieldList == "" )
{
foreach ( DataColumn dc in destRow.Table.Columns )
{
if ( dc.Expression == "" )
{
destRow[ dc ] = sourceRow[ dc.ColumnName ];
}
}
}
el
{
foreach ( FieldInfo field in m_FieldInfo )
{
destRow[ field.FieldAlias ] = sourceRow[ field.FieldName ]; }
}
destTable.Rows.Add( destRow );
}
}
private void ParGroupByFieldList(string FieldList)
{
if ( GroupByFieldList == FieldList )
{
return;
}
GroupByFieldInfo = new ArrayList();
FieldInfo Field;
string[] FieldParts;
string[] Fields = FieldList.Split( ',' );
for ( int i = 0; i <= Fields.Length - 1; i++ )
{
Field = new FieldInfo();
FieldParts = Fields[ i ].Trim().Split( ' ' );
switch ( FieldParts.Length )
{
ca 1:
//to be t at the end of the loop
break;
ca 2:
Field.FieldAlias = FieldParts[ 1 ];
break;
default:
return;
}
FieldParts = FieldParts[ 0 ].Split( '(' );
switch ( FieldParts.Length )
{
ca 1:
Field.FieldName = FieldParts[ 0 ];
break;
ca 2:
Field.Aggregate = FieldParts[ 0 ].Trim().ToLower();
Field.FieldName = FieldParts[ 1 ].Trim( ' ', ')' );
break;
default:
return;
}
if ( Field.FieldAlias == null )
if ( Field.Aggregate == null )
{
Field.FieldAlias = Field.FieldName;
}
el
{
Field.FieldAlias = Field.Aggregate + "of" + Field.FieldName;
}
}
GroupByFieldInfo.Add( Field );
}
GroupByFieldList = FieldList;
}
private DataTable CreateGroupByTable(string tableName, DataTable sourceTable, string fieldList) {
if ( fieldList == null || fieldList.Length == 0 )
{
return sourceTable.Clone();
}
el
{
DataTable dt = new DataTable( tableName );
ParGroupByFieldList( fieldList );
foreach ( FieldInfo Field in GroupByFieldInfo )
{
DataColumn dc = sourceTable.Columns[ Field.FieldName ];
if ( Field.Aggregate == null )
{
dt.Columns.Add( Field.FieldAlias, dc.DataType, dc.Expression );
}
知识与技能
el
{
dt.Columns.Add( Field.FieldAlias, dc.DataType );
}
}
if ( ds != null )
{
ds.Tables.Add( dt );
}
return dt;
}
}
private void InrtGroupByInto(DataTable destTable, DataTable sourceTable, string fieldList,
string rowFilter, string groupBy)
绿的多音字{
if ( fieldList == null || fieldList.Length == 0 )
{
return;
}
ParGroupByFieldList( fieldList );
ParFieldList( groupBy, fal );
DataRow[] rows = sourceTable.Select( rowFilter, groupBy );
DataRow lastSourceRow = null, destRow = null;
bool sameRow;
远观而不可亵玩焉int rowCount = 0;
foreach ( DataRow sourceRow in rows )
{
sameRow = fal;
if ( lastSourceRow != null )
{
sameRow = true;
foreach ( FieldInfo Field in m_FieldInfo )
{
if ( !ColumnEqual( lastSourceRow[ Field.FieldName ], sourceRow[ Field.FieldName ] ) ) {
sameRow = fal;
break;
}
}
if ( !sameRow )
{
destTable.Rows.Add( destRow );
}
}
if ( !sameRow )
{
destRow = destTable.NewRow();
rowCount = 0;
}
rowCount += 1;
foreach ( FieldInfo field in GroupByFieldInfo )
switch ( field.Aggregate.ToLower() )
{
ca null:
ca "":
ca "last":
destRow[ field.FieldAlias ] = sourceRow[ field.FieldName ];
break;
ca "first":
if ( rowCount == 1 )
{
destRow[ field.FieldAlias ] = sourceRow[ field.FieldName ];
}
break;
ca "count":
destRow[ field.FieldAlias ] = rowCount;
break;
ca "sum":
destRow[ field.FieldAlias ] = Add( destRow[ field.FieldAlias ], sourceRow[ field.FieldName ] ); break;
ca "max":
destRow[ field.FieldAlias ] = Max( destRow[ field.FieldAlias ], sourceRow[ field.FieldName ] ); break;
ca "min":
if ( rowCount == 1 )
{
destRow[ field.FieldAlias ] = sourceRow[ field.FieldName ];
}
el
{
destRow[ field.FieldAlias ] = Min( destRow[ field.FieldAlias ], sourceRow[ field.FieldName ] ); }
break;
}
}
lastSourceRow = sourceRow;
}
if ( destRow != null )
{
destTable.Rows.Add( destRow );
}
}
private object Min(object a, object b)
{
if ( ( a is DBNull ) || ( b is DBNull ) )
{
return DBNull.Value;
}
if ( ( (IComparable) a ).CompareTo( b ) == -1 )
{
return a;
}
el
{
return b;
}
}
private object Max(object a, object b)
{
if ( a is DBNull )
{
return b;
}
if ( b is DBNull )
{
return a;
}
if ( ( (IComparable) a ).CompareTo( b ) == 1 )
{
return a;
}
el
{
return b;
}
}
private object Add(object a, object b)
{
if ( a is DBNull )
{
return b;
