public virtual string PagingBuild(ref PartedSql partedSql, object args, long skip, long take)
{
if (string.IsNullOrEmpty(partedSql.OrderBy))
{
throw new InvalidOperationException("miss order by");
}
var hasDistinct = partedSql.Select.IndexOf("DISTINCT", StringComparison.OrdinalIgnoreCase) == 0;
var select = "SELECT";
if (hasDistinct)
{
partedSql.Select = partedSql.Select.Substring("DISTINCT".Length);
select = "SELECT DISTINCT";
}
if (skip <= 0)
{
var sbSql = StringBuilderCache.Allocate().AppendFormat("{0} TOP {1} {2}", select, take, partedSql.Select)
.Append(" FROM ").Append(partedSql.Body).Append(" order by ").Append(partedSql.OrderBy);
return(StringBuilderCache.ReturnAndFree(sbSql));
}
else
{
var sbSql = StringBuilderCache.Allocate()
.AppendFormat("SELECT * FROM (SELECT {0}, ROW_NUMBER() OVER " +
"(order by {1}) As RowNum FROM {2}) AS RowConstrainedResult " +
"WHERE RowNum > {3} AND RowNum <= {4}",
partedSql.Select, partedSql.OrderBy, partedSql.Body, skip, skip + take);
return(StringBuilderCache.ReturnAndFree(sbSql));
}
}
/// <summary>
/// 分割SQL
/// </summary>
public static PartedSql SplitSql(string sql)
{
var parts = new PartedSql {
Raw = sql
};
// Extract the sql from "SELECT <whatever> FROM"
var s = _rexSelect1.Match(sql);
if (s.Success)
{
parts.Select = s.Groups[1].Value;
sql = sql.Substring(s.Length);
s = _rexOrderBy.Match(sql);
if (s.Success)
{
sql = sql.Substring(0, s.Index);
parts.OrderBy = s.Groups[1].Value;
}
parts.Body = "(" + sql;
return(parts);
}
var m = _rexSelect.Match(sql);
if (!m.Success)
{
throw new ArgumentException("Unable to parse SQL statement for select");
}
parts.Select = m.Groups[1].Value;
sql = sql.Substring(m.Length);
m = _rexOrderBy.Match(sql);
if (m.Success)
{
sql = sql.Substring(0, m.Index);
parts.OrderBy = m.Groups[1].Value;
}
parts.Body = sql;
return(parts);
}
public virtual string PagingBuild(ref PartedSql partedSql, object args, long skip, long take)
{
var pageSql = $"{partedSql.Raw} LIMIT {take} OFFSET {skip}";
return(pageSql);
}
public static string GetCountSql(PartedSql sql)
{
return($"SELECT COUNT(*) FROM {sql.Body}");
}