DataTable Group By实现统计

个db个db group 

:db,
dbgroup

dbgroup

dbgroup

dbgroup

的linq

var query = from c in dt.AsEnumerable()
group c by new {
custom = c.Field<string>("客户名称"),
num = c.Field<string>("客户编码")
}
into s
select new {
custom = s.Select(p => p.Field<string>("客户名称")).First(),
shuliang = s.Sum(p => Convert.ToInt32(p.Field<string>("销售数量"))),
num = s.Select(p => p.Field<string>("客户编码")).First(),
biaohao = string.Join(";", s.Select(p => p.Field<string>("id")))
};

DataTable tbl = dt.Clone();
query.ToList().ForEach(p => tbl.Rows.Add(p.custom, p.num, p.biaohao, p.shuliang));

便

  sum/avg/min/max,和group的db
/*调用此方法就可以了

*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;
}*/

 
/*
* author 橙子
* time 2015年8月13日13:23:25
* info db.SelectGroupByInto("客户分类汇总", hzmxTable, "客户编码,客户名称,商品编号,石材名称,产品名称,厚mm,sum(销售数量) 销售数量,sum(销售金额) 销售金额,sum(退货数量) 退货数量,sum(退货金额) 退货金额,sum(加工费) 加工费,sum(销售加工) 销售加工,单位", "", "套账编号,客户编码,客户简码,客户名称,商品编号,石材简码,石材名称,产品名称,厚mm,单位,null1");
*
* 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"
* 根据相关的sql关键字 做相应的处理 这里处理函数需要自己写 若Aggregate 为空 则返回统计外的字段
*
*
*/
namespace Common.DB {
public class DbsetHelper {
private class FieldInfo {
public string RelationName;
public string FieldName;
public string FieldAlias;
public string Aggregate;
}

private DataSet _ds;
private ArrayList _mFieldInfo;
private string _mFieldList;
private ArrayList _groupByFieldInfo;
private string _groupByFieldList;

public DataSet DataSet {
get { return _ds; }
}
#region Construction

public DbsetHelper() {
_ds = null;
}

public DbsetHelper(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 (_mFieldList == fieldList) {
return;
}
_mFieldInfo = new ArrayList();
_mFieldList = 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;
}
_mFieldInfo.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 _mFieldInfo) {
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 InsertInto(DataTable destTable, DataTable sourceTable,
string fieldList, string rowFilter, string sort) {
ParseFieldList(fieldList, false);
DataRow[] rows = sourceTable.Select(rowFilter, sort);
foreach (DataRow sourceRow in rows) {
var 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 _mFieldInfo) {
destRow[field.FieldAlias] = sourceRow[field.FieldName];
}
}
destTable.Rows.Add(destRow);
}
}

private void ParseGroupByFieldList(string FieldList) {
if (_groupByFieldList == FieldList) {
return;
}
_groupByFieldInfo = new ArrayList();
string[] fields = FieldList.Split(',');
for (int i = 0; i <= fields.Length - 1; i++) {
var field = new FieldInfo();
var 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 (string.IsNullOrEmpty(fieldList)) {
return sourceTable.Clone();
}
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 (string.IsNullOrEmpty(fieldList)) {
return;
}
ParseGroupByFieldList(fieldList);
ParseFieldList(groupBy, false);
DataRow[] rows = sourceTable.Select(rowFilter, groupBy);
DataRow lastSourceRow = null, destRow = null;
int rowCount = 0;
foreach (DataRow sourceRow in rows) {
var sameRow = false;
if (lastSourceRow != null){
sameRow = _mFieldInfo.Cast<FieldInfo>().All(field => ColumnEqual(lastSourceRow[field.FieldName], sourceRow[field.FieldName]));
if (!sameRow) {
destTable.Rows.Add(destRow);
}
}
if (!sameRow) {
destRow = destTable.NewRow();
rowCount = 0;
}
rowCount += 1;
foreach (FieldInfo field in _groupByFieldInfo) {
switch (field.Aggregate == null ? null : field.Aggregate.ToLower()) {
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;
}
return decimal.Parse(a + "") + decimal.Parse(b + "");//为0.0
}

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 _mFieldInfo) {
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 _mFieldInfo) {
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 Select Distinct
/// <summary>
/// 照fieldName从sourceTable
/// 于select distinct fieldName from sourceTable
/// </summary>
/// <param name="tableName"></param>
/// <param name="sourceTable">源DataTable</param>
/// <param name="fieldName"></param>
/// <returns>的DataTable括fieldName</returns>
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;
}

/// <summary>
/// 照fieldName从sourceTable
/// 于select distinct fieldName1,fieldName2,,fieldNamen from sourceTable
/// </summary>
/// <param name="tableName"></param>
/// <param name="sourceTable">源DataTable</param>
/// <param name="fieldNames"></param>
/// <returns>的DataTable括fieldNames</returns>
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;
}
/// <summary>
/// 照fieldName从sourceTable
/// 含sourceTable
/// </summary>
/// <param name="tableName"></param>
/// <param name="sourceTable"></param>
/// <param name="fieldName"></param>
/// <returns>的DataTable</returns>
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;
}

/// <summary>
/// 照fieldNames从sourceTable
/// 含sourceTable
/// </summary>
/// <param name="tableName"></param>
/// <param name="sourceTable"></param>
/// <param name="fieldNames"></param>
/// <returns>的DataTable</returns>
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

[reply]
/// <summary>
/// 按sort按rowFilter滤sourceTable
/// 制fieldList新DataTable
/// </summary>
/// <param name="tableName"></param>
/// <param name="sourceTable"></param>
/// <param name="fieldList"></param>
/// <param name="rowFilter"></param>
/// <param name="sort"></param>
/// <returns>新DataTable</returns>
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 Table Group By

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);
string[] fields = fieldList.Split(',');
foreach (string field in fields) {
var fieldsParts = field.Trim().Split(" ".ToCharArray(), 3);
// add fieldname and datatype
DataColumn dc;
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
{
var 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
}
}[/reply]

 

未经允许不得转载:聚艺帮 » DataTable Group By实现统计

分享到:更多 ()

牛评 抢沙发

爱生活 爱设计

齐聚智慧联系我们