/// <summary>
/// 解析SQL语句中的Select 字段信息，并返回 From 谓词在SQL语句中的位置索引
/// </summary>
/// <param name="sql">SQL语句</param>
/// <param name="fromIndex">From 谓词在SQL语句中的位置索引</param>
/// <returns>Select 字段信息</returns>
public List <SqlField> ParseSelect(string sql, out int fromIndex)
{
Point ps = TextSearchUtil.SearchWordsIndex(sql, "select");
if (ps.A == -1)
{
throw new ArgumentException("未找到期望的谓词select ,不是合法的SQL:\r\n" + sql);
}
Point pf = TextSearchUtil.SearchWordsIndex(sql, "from");
if (pf.A < ps.A)
{
throw new ArgumentException("在select谓词之后未找到期望的from谓词，不支持此种类型的SQL分页：\r\n" + sql);
}
fromIndex = pf.A;
string selectFieldsBlock = sql.Substring(ps.B, pf.A - ps.B).Trim();
List <SqlField> sqlFields = new List <SqlField>();
if (selectFieldsBlock == "*")
{
sqlFields.Add(new SqlField()
{
Field = "*"
});
return(sqlFields);
}
else
{
string[] selectFieldsArr = selectFieldsBlock.Split(new string[] { ",", Environment.NewLine }, StringSplitOptions.RemoveEmptyEntries);
foreach (string fieldItem in selectFieldsArr)
{
int targetIndex = 0;
string field = TextSearchUtil.FindNearWords(fieldItem, 0, 100, out targetIndex);
if (targetIndex >= 0)
{
//寻找临近的单词，可能没有，可能是AS，也可能直接就是字段别名
string fieldAsName = TextSearchUtil.FindNearWords(fieldItem, targetIndex + field.Length, 50, out targetIndex);
if (fieldAsName.ToLower() == "as")
{
fieldAsName = TextSearchUtil.FindNearWords(fieldItem, targetIndex + 2, 50, out targetIndex);
}
sqlFields.Add(new SqlField()
{
Field = field, Alias = fieldAsName
});
}
}
}
return(sqlFields);
}
/// <summary>
/// 解析SQL语句中的Select 字段信息，并返回 From 谓词在SQL语句中的位置索引
/// </summary>
/// <param name="sql">SQL语句</param>
/// <param name="fromIndex">From 谓词在SQL语句中的位置索引</param>
/// <returns>Select 字段信息</returns>
public List <SqlField> ParseSelect(string sql, out int fromIndex)
{
Point ps = TextSearchUtil.SearchWordsIndex(sql, "select");
if (ps.A == -1)
{
throw new ArgumentException("未找到期望的谓词select ,不是合法的SQL:\r\n" + sql);
}
Point pd = TextSearchUtil.SearchWordsIndex(sql, "DISTINCT");
if (pd.A != -1)
{
ps.B = pd.B;
}
Point pf = TextSearchUtil.SearchWordsIndex(sql, "from");
if (pf.A < ps.A)
{
throw new ArgumentException("在select谓词之后未找到期望的from谓词，不支持此种类型的SQL分页：\r\n" + sql);
}
fromIndex = pf.A;
string selectFieldsBlock = sql.Substring(ps.B, pf.A - ps.B).Trim();
List <SqlField> sqlFields = new List <SqlField>();
if (selectFieldsBlock == "*")
{
sqlFields.Add(new SqlField()
{
Field = "*"
});
return(sqlFields);
}
else
{
string[] selectFieldsArr = selectFieldsBlock.Split(new string[] { ",", Environment.NewLine }, StringSplitOptions.RemoveEmptyEntries);
foreach (string fieldItem in selectFieldsArr)
{
int targetIndex = 0;
string field = TextSearchUtil.FindNearWords(fieldItem, 0, 100, out targetIndex);
if (targetIndex >= 0)
{
//select 字段可能是函数调用，感谢网友 芜湖－大枕头 发现此Bug 2017.4.14
string fieldUp = field.ToUpper();
if (fieldUp == "SUM" || fieldUp == "AVG" || fieldUp == "MIN" || fieldUp == "MAX" || fieldUp == "COUNT" || fieldUp.IndexOf('(') > 0)
{
int atas = fieldItem.ToUpper().IndexOf(" AS ");
string fieldAsName = fieldItem.Substring(atas + 3);
sqlFields.Add(new SqlField()
{
Field = fieldItem.Substring(0, atas),
Alias = fieldAsName
});
}
else
{
//寻找临近的单词，可能没有，可能是AS，也可能直接就是字段别名
string fieldAsName = TextSearchUtil.FindNearWords(fieldItem, targetIndex + field.Length, 50, out targetIndex);
if (fieldAsName.ToLower() == "as")
{
fieldAsName = TextSearchUtil.FindNearWords(fieldItem, targetIndex + 2, 50, out targetIndex);
}
sqlFields.Add(new SqlField()
{
Field = field, Alias = fieldAsName
});
}
}
}
}
return(sqlFields);
}
/// <summary>
/// 构造可分页的排序SQL语句，如果topCount小于0，源SQL必须带排序语句
/// </summary>
/// <param name="topCount">如果指定大于0的值，将生成Top子句，如果小于0，则是限制逆向排序的记录数</param>
/// <returns></returns>
public string Build(int topCount)
{
int fromIndex;
Point orderBlockPoint;
bool isSelectStart = false;
List <SqlField> sqlFields = ParseSelect(this.sourceSql, out fromIndex);
this.OrderFields = ParseOrder(this.sourceSql, out orderBlockPoint);
if (sqlFields[0].Field == "*")
{
isSelectStart = true;
}
else if (this.OrderFields != null)
{
//检查参与排序的字段是否在SELECT中，如果不在，添加进去
foreach (OrderField orderItem in this.OrderFields)
{
var target = sqlFields.Find(p => string.Equals(p.Field, orderItem.Field, StringComparison.OrdinalIgnoreCase) ||
string.Equals(p.Alias, orderItem.Field, StringComparison.OrdinalIgnoreCase));
if (target == null)
{
orderItem.CreateAlias("");
var tempField = new SqlField()
{
Field = orderItem.Field, Alias = orderItem.Alias
};
sqlFields.Add(tempField);
orderItem.InSelect = false;
}
else
{
orderItem.Field = target.Field;
orderItem.Alias = target.Alias;
orderItem.InSelect = true;
}
}
}
this.SelectFields = sqlFields;
//重新构造Select语句块
System.Text.StringBuilder sb = new StringBuilder();
//DISTINCT 查询问题，感谢网友 @深圳-光头佬 发现此问题 2017.6.21
Point psd = TextSearchUtil.SearchWordsIndex(sourceSql, "select DISTINCT");
if (psd.A != -1)
{
sb.Append("SELECT DISTINCT ");
}
else
{
sb.Append("SELECT ");
}
if (topCount > 0)
{
sb.Append("Top " + topCount + "\r\n");
}
else if (topCount < 0)
{
sb.Append("Top " + (-topCount) + "\r\n");
}
else
{
sb.Append(" \r\n");
}
if (isSelectStart)
{
sb.Append(" * \r\n");
}
else
{
int count = this.SelectFields.Count;
int index = 0;
foreach (SqlField item in this.SelectFields)
{
index++;
sb.Append('\t');
sb.Append(item.Field);
if (!string.IsNullOrEmpty(item.Alias))
{
sb.Append(" AS ");
sb.Append(item.Alias);
}
if (index != count)
{
sb.Append(',');
}
sb.Append("\r\n");
}
}
if (topCount < 0)
{
//逆排序，用于获取最后几页的数据，包括最后一页
if (orderBlockPoint.A == -1)
{
throw new Exception("当参数 topCount小于0（逆排序），必须指定排序信息");
}
//改变Order by 子句，生成逆向排序查询
string beforOrder = this.sourceSql.Substring(fromIndex, orderBlockPoint.A - fromIndex);
string afterOrder = this.sourceSql.Substring(orderBlockPoint.B);
string InverseOrderString = "\r\n ORDER BY " + this.GetLastInverseOrderExpString() + "\r\n";
sb.Append(beforOrder);
sb.Append(InverseOrderString);
sb.Append(afterOrder);
}
else
{
sb.Append(this.sourceSql.Substring(fromIndex));
}
return(sb.ToString());
}