/// <summary>
/// 分组查询，返回datatable.
/// </summary>
/// <param name="attrsOfGroupKey"></param>
/// <param name="groupValField"></param>
/// <param name="gw"></param>
/// <returns></returns>
public DataTable DoGroupReturnTable1(Entity en, Attrs attrsOfGroupKey, Attr attrGroup, GroupWay gw, OrderWay ow)
{
#region 生成要查询的语句
string fields = "";
string str = "";
foreach (Attr attr in attrsOfGroupKey)
{
if (attr.Field == null)
{
continue;
}
str = "," + attr.Field;
fields += str;
}
if (attrGroup.Key == "MyNum")
{
switch (gw)
{
case GroupWay.BySum:
fields += ", COUNT(*) AS MyNum";
break;
case GroupWay.ByAvg:
fields += ", AVG(*) AS MyNum";
break;
default:
throw new Exception("no such case:");
}
}
else
{
switch (gw)
{
case GroupWay.BySum:
fields += ",SUM(" + attrGroup.Field + ") AS " + attrGroup.Key;
break;
case GroupWay.ByAvg:
fields += ",AVG(" + attrGroup.Field + ") AS " + attrGroup.Key;
break;
default:
throw new Exception("no such case:");
}
}
string by = "";
foreach (Attr attr in attrsOfGroupKey)
{
if (attr.Field == null)
{
continue;
}
str = "," + attr.Field;
by += str;
}
by = by.Substring(1);
//string sql
string sql = "SELECT " + fields.Substring(1) + " FROM " + this.En.EnMap.PhysicsTable + " WHERE " + this._sql + " Group BY " + by;
#endregion
#region
Map map = new Map();
map.PhysicsTable = "@VT@";
map.Attrs = attrsOfGroupKey;
map.Attrs.Add(attrGroup);
#endregion .
//string sql1=SqlBuilder.SelectSQLOfMS( map )+" "+SqlBuilder.GenerFormWhereOfMS( en,map) + " AND ( " + this._sql+" ) "+_endSql;
string sql1 = SqlBuilder.SelectSQLOfMS(map) + " " + SqlBuilder.GenerFormWhereOfMS(en, map);
sql1 = sql1.Replace("@TopNum", "");
sql1 = sql1.Replace("FROM @VT@", "FROM (" + sql + ") VT");
sql1 = sql1.Replace("@VT@", "VT");
sql1 = sql1.Replace("TOP", "");
if (ow == OrderWay.OrderByUp)
{
sql1 += " ORDER BY " + attrGroup.Key + " DESC ";
}
else
{
sql1 += " ORDER BY " + attrGroup.Key;
}
return(DBAccess.RunSQLReturnTable(sql1));
}
public DataTable DoGroupReturnTable(Entity en, Attrs attrsOfGroupKey, Attr attrGroup, GroupWay gw, OrderWay ow)
{
switch (en.EnMap.EnDBUrl.DBType)
{
case DBType.Oracle:
return(DoGroupReturnTableOracle(en, attrsOfGroupKey, attrGroup, gw, ow));
default:
return(DoGroupReturnTableSqlServer(en, attrsOfGroupKey, attrGroup, gw, ow));
}
}
