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