/// <summary> /// 拆分大查询 /// </summary> private void SubQuery() { // DateTime midtime, UTCStartTime, UTCEndTime; midtime = QueryStartTime.ToUniversalTime(); UTCStartTime = QueryStartTime.ToUniversalTime(); UTCEndTime = QueryEndTime.ToUniversalTime(); //1.得到所有跨越的月 _times.Add(QueryStartTime.ToString("yyyy-MM-dd HH:mm:ss")); _tables.Add("taglog" + midtime.ToString("yyyyMM")); do { midtime = DateTime.Parse(midtime.AddMonths(1).ToString("yyyy-MM-01 00:00:00")); if (midtime <= UTCEndTime) { _times.Add(midtime.ToLocalTime().ToString("yyyy-MM-dd 07:59:00")); _tables.Add("taglog" + midtime.ToString("yyyyMM")); } }while (midtime <= UTCEndTime); _times.Add(QueryEndTime.ToString("yyyy-MM-dd HH:mm:ss")); //2.组织tags string inStatement = ""; foreach (string tag in QueryTags) { inStatement = inStatement + "'" + tag + "',"; } inStatement = inStatement.Substring(0, inStatement.Length - 1); //3.组织select语句 List <string> selects = new List <string> { }; for (int i = 0; i < _times.Count - 1; i++) { if (i == 0) { string select = " SELECT `Time` ,`TagName`,`Value` From " + _tables[i] + " FORCE INDEX (name_time) " + //强制使用索引 " WHERE time between '" + _times[i] + "' and '" + _times[i + 1] + "' and timestampdiff(minute,'" + _times[0] + "',time) %" + QueryStep + "=0 and tagname in (" + inStatement + "); "; selects.Add(select); } else { string select = " SELECT `Time` ,`TagName`,`Value` From " + _tables[i] + " FORCE INDEX (name_time) " + //强制使用索引 " WHERE time between '" + DateTime.Parse(_times[i]).AddMinutes(1).ToString("yyy-MM-dd HH:mm:ss") + "' and '" + _times[i + 1] + "' and timestampdiff(minute,'" + _times[0] + "',time) %" + QueryStep + "=0 and tagname in (" + inStatement + "); "; selects.Add(select); } } //4.创建query foreach (string select in selects) { BaseQuery bq = new BaseQuery(connectString, select); Querys.Add(bq); } }
/// <summary> /// 分割查询 /// </summary> private void SubQuery() { //1.组织时间点和月份以及表名对应关系 List <string> mMonth = new List <string> { }; foreach (DateTime dt in QueryTimes) { mMonth.Add(dt.ToString("yyyy-MM-01 08:00:00")); } mMonth = mMonth.Distinct().ToList(); foreach (string month in mMonth) { TimePointMonth tm = new TimePointMonth(); tm.TableName = "taglog" + DateTime.Parse(month).ToString("yyyyMM"); foreach (DateTime dt in QueryTimes) { if ((DateTime.Parse(month).Year == dt.Year) && (DateTime.Parse(month).Month == dt.Month)) { tm.TimePoint.Add(dt); } } _months.Add(tm); } //2.组织tags string inStatement = ""; foreach (string tag in QueryTags) { inStatement = inStatement + "'" + tag + "',"; } inStatement = inStatement.Substring(0, inStatement.Length - 1); //3.组织select语句 List <string> selects = new List <string> { }; for (int i = 0; i < _months.Count; i++) { string times = " WHERE time in ( "; foreach (DateTime dt in _months[i].TimePoint) { times = times + "'" + dt.ToString("yyyy-MM-dd HH:mm:ss") + "',"; } times = times.Substring(0, times.Length - 1) + ")"; string select = " SELECT `Time` ,`TagName`,`Value` From " + _months[i].TableName + times + " and tagname in (" + inStatement + "); "; selects.Add(select); } //4.创建query foreach (string select in selects) { BaseQuery bq = new BaseQuery(connectString, select); Querys.Add(bq); } }