[转]DataTable 使用 Distinct/group by/

转自:http://www.cnblogs.com/jinweida/archive/2009/03/16/1412919.html

 

using System;using System.Collections.Generic;using System.Text;using System.Collections;using System.Data;namespace DataSetHelper{    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; }        }        #region Construction        public DataSetHelper()        {            ds = null;        }        public DataSetHelper(ref DataSet dataSet)        {            ds = dataSet;        }        #endregion        #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 false;            }            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 ParseFieldList(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)                {                    case 1:                        //to be set at the end of the loop                         break;                    case 2:                        Field.FieldAlias = FieldParts[1];                        break;                    default:                        return;                }                FieldParts = FieldParts[0].Split('.');                switch (FieldParts.Length)                {                    case 1:                        Field.FieldName = FieldParts[0];                        break;                    case 2:                        if (allowRelation == false)                        {                            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;            }            else            {                dt = new DataTable(tableName);                ParseFieldList(fieldList, false);                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;        }        ///         /// 合并结构相同的表        ///         /// 源表1        /// 源表2        /// 返回表        public DataTable JoinTable(DataTable sDTone, DataTable sDTtwo)        {            try            {                DataTable dt = sDTone.Clone();                object[] obj = new object[dt.Columns.Count];                for (int i = 0; i < sDTone.Rows.Count; i++)                {                    sDTone.Rows[i].ItemArray.CopyTo(obj, 0);                    dt.Rows.Add(obj);                }                for (int i = 0; i < sDTtwo.Rows.Count; i++)                {                    sDTtwo.Rows[i].ItemArray.CopyTo(obj, 0);                    dt.Rows.Add(obj);                }                return dt;            }            catch (Exception ex)            {                System.Console.Error.Write(ex.Message);                return null;            }        }        private void InsertInto(DataTable destTable, DataTable sourceTable,                                string fieldList, string rowFilter, string sort)        {            ParseFieldList(fieldList, false);            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];                        }                    }                }                else                {                    foreach (FieldInfo field in m_FieldInfo)                    {                        destRow[field.FieldAlias] = sourceRow[field.FieldName];                    }                }                destTable.Rows.Add(destRow);            }        }        private void ParseGroupByFieldList(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)                {                    case 1:                        //to be set at the end of the loop                         break;                    case 2:                        Field.FieldAlias = FieldParts[1];                        break;                    default:                        return;                }                FieldParts = FieldParts[0].Split('(');                switch (FieldParts.Length)                {                    case 1:                        Field.FieldName = FieldParts[0];                        break;                    case 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;                    }                    else                    {                        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();            }            else            {                DataTable dt = new DataTable(tableName);                ParseGroupByFieldList(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);                    }                    else                    {                        dt.Columns.Add(Field.FieldAlias, dc.DataType);                    }                }                if (ds != null)                {                    ds.Tables.Add(dt);                }                return dt;            }        }        private void InsertGroupByInto(DataTable destTable, DataTable sourceTable, string fieldList,                                       string rowFilter, string groupBy)        {            if (fieldList == null || fieldList.Length == 0)            {                return;            }            ParseGroupByFieldList(fieldList);            ParseFieldList(groupBy, false);            DataRow[] rows = sourceTable.Select(rowFilter, groupBy);            DataRow lastSourceRow = null, destRow = null;            bool sameRow;            int rowCount = 0;            foreach (DataRow sourceRow in rows)            {                sameRow = false;                if (lastSourceRow != null)                {                    sameRow = true;                    foreach (FieldInfo Field in m_FieldInfo)                    {                        if (!ColumnEqual(lastSourceRow[Field.FieldName], sourceRow[Field.FieldName]))                        {                            sameRow = false;                            break;                        }                    }                    if (!sameRow)                    {                        destTable.Rows.Add(destRow);                    }                }                if (!sameRow)                {                    destRow = destTable.NewRow();                    rowCount = 0;                }                rowCount += 1;                foreach (FieldInfo field in GroupByFieldInfo)                {                    switch (field.Aggregate)                    {                        case null:                        case "":                        case "last":                            destRow[field.FieldAlias] = sourceRow[field.FieldName];                            break;                        case "first":                            if (rowCount == 1)                            {                                destRow[field.FieldAlias] = sourceRow[field.FieldName];                            }                            break;                        case "count":                            destRow[field.FieldAlias] = rowCount;                            break;                        case "sum":                            destRow[field.FieldAlias] = Add(destRow[field.FieldAlias], sourceRow[field.FieldName]);                            break;                        case "max":                            destRow[field.FieldAlias] = Max(destRow[field.FieldAlias], sourceRow[field.FieldName]);                            break;                        case "min":                            if (rowCount == 1)                            {                                destRow[field.FieldAlias] = sourceRow[field.FieldName];                            }                            else                            {                                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;            }            else            {                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;            }            else            {                return b;            }        }        private object Add(object a, object b)        {            if (a is DBNull)            {                return b;            }            if (b is DBNull)            {                return a;            }            decimal d1 = 0,d2=0;            decimal.TryParse(a.ToString(), out d1);            decimal.TryParse(b.ToString(), out d2);            return d1+d2;        }        private DataTable CreateJoinTable(string tableName, DataTable sourceTable, string fieldList)        {            if (fieldList == null)            {                return sourceTable.Clone();            }            else            {                DataTable dt = new DataTable(tableName);                ParseFieldList(fieldList, true);                foreach (FieldInfo field in m_FieldInfo)                {                    if (field.RelationName == null)                    {                        DataColumn dc = sourceTable.Columns[field.FieldName];                        dt.Columns.Add(dc.ColumnName, dc.DataType, dc.Expression);                    }                    else                    {                        DataColumn dc = sourceTable.ParentRelations[field.RelationName].ParentTable.Columns[field.FieldName];                        dt.Columns.Add(dc.ColumnName, dc.DataType, dc.Expression);                    }                }                if (ds != null)                {                    ds.Tables.Add(dt);                }                return dt;            }        }        private void InsertJoinInto(DataTable destTable, DataTable sourceTable,                                    string fieldList, string rowFilter, string sort)        {            if (fieldList == null)            {                return;            }            else            {                ParseFieldList(fieldList, true);                DataRow[] Rows = sourceTable.Select(rowFilter, sort);                foreach (DataRow SourceRow in Rows)                {                    DataRow DestRow = destTable.NewRow();                    foreach (FieldInfo Field in m_FieldInfo)                    {                        if (Field.RelationName == null)                        {                            DestRow[Field.FieldName] = SourceRow[Field.FieldName];                        }                        else                        {                            DataRow ParentRow = SourceRow.GetParentRow(Field.RelationName);                            DestRow[Field.FieldName] = ParentRow[Field.FieldName];                        }                    }                    destTable.Rows.Add(DestRow);                }            }        }        #endregion        #region SelectDistinct / Distinct        /**/        /**/        /**/        ///          /// 按照fieldName从sourceTable中选择出不重复的行，         /// 相当于select distinct fieldName from sourceTable         ///          /// 表名         /// 源DataTable         /// 列名         /// 一个新的不含重复行的DataTable，列只包括fieldName指明的列         public DataTable SelectDistinct(string tableName, DataTable sourceTable, string fieldName)        {            DataTable dt = new DataTable(tableName);            dt.Columns.Add(fieldName, sourceTable.Columns[fieldName].DataType);            object lastValue = null;            foreach (DataRow dr in sourceTable.Select("", fieldName))            {                if (lastValue == null || !(ColumnEqual(lastValue, dr[fieldName])))                {                    lastValue = dr[fieldName];                    dt.Rows.Add(new object[] { lastValue });                }            }            if (ds != null && !ds.Tables.Contains(tableName))            {                ds.Tables.Add(dt);            }            return dt;        }        /**/        /**/        /**/        ///          /// 按照fieldName从sourceTable中选择出不重复的行，         /// 相当于select distinct fieldName1,fieldName2,,fieldNamen from sourceTable         ///          /// 表名         /// 源DataTable         /// 列名数组         /// 一个新的不含重复行的DataTable，列只包括fieldNames中指明的列         public DataTable SelectDistinct(string tableName, DataTable sourceTable, string[] fieldNames)        {            DataTable dt = new DataTable(tableName);            object[] values = new object[fieldNames.Length];            string fields = "";            for (int i = 0; i < fieldNames.Length; i++)            {                dt.Columns.Add(fieldNames[i], sourceTable.Columns[fieldNames[i]].DataType);                fields += fieldNames[i] + ",";            }            fields = fields.Remove(fields.Length - 1, 1);            DataRow lastRow = null;            foreach (DataRow dr in sourceTable.Select("", fields))            {                if (lastRow == null || !(RowEqual(lastRow, dr, dt.Columns)))                {                    lastRow = dr;                    for (int i = 0; i < fieldNames.Length; i++)                    {                        values[i] = dr[fieldNames[i]];                    }                    dt.Rows.Add(values);                }            }            if (ds != null && !ds.Tables.Contains(tableName))            {                ds.Tables.Add(dt);            }            return dt;        }        /**/        /**/        /**/        ///          /// 按照fieldName从sourceTable中选择出不重复的行，         /// 并且包含sourceTable中所有的列。         ///          /// 表名         /// 源表         /// 字段         /// 一个新的不含重复行的DataTable         public DataTable Distinct(string tableName, DataTable sourceTable, string fieldName)        {            DataTable dt = sourceTable.Clone();            dt.TableName = tableName;            object lastValue = null;            foreach (DataRow dr in sourceTable.Select("", fieldName))            {                if (lastValue == null || !(ColumnEqual(lastValue, dr[fieldName])))                {                    lastValue = dr[fieldName];                    dt.Rows.Add(dr.ItemArray);                }            }            if (ds != null && !ds.Tables.Contains(tableName))            {                ds.Tables.Add(dt);            }            return dt;        }        /**/        /**/        /**/        ///          /// 按照fieldNames从sourceTable中选择出不重复的行，         /// 并且包含sourceTable中所有的列。         ///          /// 表名         /// 源表         /// 字段         /// 一个新的不含重复行的DataTable         public DataTable Distinct(string tableName, DataTable sourceTable, string[] fieldNames)        {            DataTable dt = sourceTable.Clone();            dt.TableName = tableName;            string fields = "";            for (int i = 0; i < fieldNames.Length; i++)            {                fields += fieldNames[i] + ",";            }            fields = fields.Remove(fields.Length - 1, 1);            DataRow lastRow = null;            foreach (DataRow dr in sourceTable.Select("", fields))            {                if (lastRow == null || !(RowEqual(lastRow, dr, dt.Columns)))                {                    lastRow = dr;                    dt.Rows.Add(dr.ItemArray);                }            }            if (ds != null && !ds.Tables.Contains(tableName))            {                ds.Tables.Add(dt);            }            return dt;        }        #endregion        #region Select Table Into        /**/        /**/        /**/        ///          /// 按sort排序，按rowFilter过滤sourceTable，         /// 复制fieldList中指明的字段的数据到新DataTable，并返回之         ///          /// 表名         /// 源表         /// 字段列表         /// 过滤条件         /// 排序         /// 新DataTable         public DataTable SelectInto(string tableName, DataTable sourceTable,                                    string fieldList, string rowFilter, string sort)        {            DataTable dt = CreateTable(tableName, sourceTable, fieldList);            InsertInto(dt, sourceTable, fieldList, rowFilter, sort);            return dt;        }        #endregion        #region Group By Table        public DataTable SelectGroupByInto(string tableName, DataTable sourceTable, string fieldList,                                           string rowFilter, string groupBy)        {            DataTable dt = CreateGroupByTable(tableName, sourceTable, fieldList);            InsertGroupByInto(dt, sourceTable, fieldList, rowFilter, groupBy);            return dt;        }        #endregion        #region Join Tables        public DataTable SelectJoinInto(string tableName, DataTable sourceTable, string fieldList, string rowFilter, string sort)        {            DataTable dt = CreateJoinTable(tableName, sourceTable, fieldList);            InsertJoinInto(dt, sourceTable, fieldList, rowFilter, sort);            return dt;        }        #endregion        #region Create Table        public DataTable CreateTable(string tableName, string fieldList)        {            DataTable dt = new DataTable(tableName);            DataColumn dc;            string[] Fields = fieldList.Split(',');            string[] FieldsParts;            string Expression;            foreach (string Field in Fields)            {                FieldsParts = Field.Trim().Split(" ".ToCharArray(), 3); // allow for spaces in the expression                 // add fieldname and datatype                 if (FieldsParts.Length == 2)                {                    dc = dt.Columns.Add(FieldsParts[0].Trim(), Type.GetType("System." + FieldsParts[1].Trim(), true, true));                    dc.AllowDBNull = true;                }                else if (FieldsParts.Length == 3) // add fieldname, datatype, and expression                 {                    Expression = FieldsParts[2].Trim();                    if (Expression.ToUpper() == "REQUIRED")                    {                        dc = dt.Columns.Add(FieldsParts[0].Trim(), Type.GetType("System." + FieldsParts[1].Trim(), true, true));                        dc.AllowDBNull = false;                    }                    else                    {                        dc = dt.Columns.Add(FieldsParts[0].Trim(), Type.GetType("System." + FieldsParts[1].Trim(), true, true), Expression);                    }                }                else                {                    return null;                }            }            if (ds != null)            {                ds.Tables.Add(dt);            }            return dt;        }        public DataTable CreateTable(string tableName, string fieldList, string keyFieldList)        {            DataTable dt = CreateTable(tableName, fieldList);            string[] KeyFields = keyFieldList.Split(',');            if (KeyFields.Length > 0)            {                DataColumn[] KeyFieldColumns = new DataColumn[KeyFields.Length];                int i;                for (i = 1; i == KeyFields.Length - 1; ++i)                {                    KeyFieldColumns[i] = dt.Columns[KeyFields[i].Trim()];                }                dt.PrimaryKey = KeyFieldColumns;            }            return dt;        }        #endregion        #region        //    dt = dsHelper.SelectGroupByInto("OrderSummary", ds.Tables["Employees"],        //"EmployeeID,sum(Amount) Total,min(Amount) Min,max(Amount) Max", "EmployeeID<5", "EmployeeID");        #endregion    }} 
==========================================================================================================

调用:

DataTable dt1 = new DataTable();dt1.Columns.Add( new DataColumn("收款台",typeof(string)));dt1.Columns.Add(new DataColumn("收银员", typeof(string)));dt1.Columns.Add(new DataColumn("卡类别", typeof(string)));dt1.Columns.Add(new DataColumn("金额类别", typeof(string)));dt1.Columns.Add(new DataColumn("金额", typeof(float)));dt1.Rows.Add("柜台A","小王","现金","现金金额",345);dt1.Rows.Add("柜台A", "小王", "银行卡", "金额", 345);dt1.Rows.Add("柜台A", "小王", "现金", "现金金额", 345);dt1.Rows.Add("柜台A", "小王", "银行卡", "金额", -45);dt1.Rows.Add("柜台A", "小王", "现金", "现金金额", 345);dt1.Rows.Add("柜台B", "小天", "现金", "现金金额", 345);DataSetHelper.DataSetHelper ds = new DataSetHelper.DataSetHelper();DataTable dt2= ds.SelectGroupByInto("sdf", dt1, "收款台,收银员,卡类别,金额类别,sum(金额)", "1=1", "收款台,收银员,卡类别,金额类别");