/// <summary>
/// 获取分页的SQL
/// </summary>
/// <param name="skip"></param>
/// <param name="take"></param>
/// <param name="parts"></param>
/// <param name="databaseType"></param>
/// <returns></returns>
public static string GetSqlPage(long skip, long take, SqlParts parts, DatabaseType databaseType)
{
string sqlPage = string.Empty;
//TODO 必须大于1 ，有些分页要求起始是1 有些是0
skip = skip > 0 ? skip : 0;
take = take > 1 ? take : 1;
switch (databaseType)
{
case DatabaseType.SqlServer2012:
if (string.IsNullOrEmpty(parts.SqlOrderBy))
{
parts.Sql += " ORDER BY (SELECT NULL)";
}
sqlPage = $"{parts.Sql} \n OFFSET @{skip} ROWS FETCH NEXT @{take} ROWS ONLY";
break;
case DatabaseType.SqlServer:
// when the query does not contain an "order by", it is very slow
if (SimpleRegexOrderBy.IsMatch(parts.SqlSelectRemoved))
{
var m = SimpleRegexOrderBy.Match(parts.SqlSelectRemoved);
if (m.Success)
{
var g = m.Groups[0];
parts.SqlSelectRemoved = parts.SqlSelectRemoved.Substring(0, g.Index);
}
}
if (RegexDistinct.IsMatch(parts.SqlSelectRemoved))
{
parts.SqlSelectRemoved = "peta_inner.* FROM (SELECT " + parts.SqlSelectRemoved + ") peta_inner";
}
sqlPage =
$"SELECT * FROM (SELECT ROW_NUMBER() OVER ({(parts.SqlOrderBy ?? "ORDER BY (SELECT NULL)")}) page_row, {parts.SqlSelectRemoved}) dapper_paged_table WHERE page_row > {skip} AND page_row <= {skip + take }";
break;
case DatabaseType.PostgreSql:
case DatabaseType.SqLite:
case DatabaseType.MySql:
sqlPage = $"{parts.Sql}\n LIMIT @{skip} OFFSET @{take}";
break;
}
return(sqlPage);
}
/// <summary>
/// Splits the given <paramref name="sql" /> into <paramref name="parts" />;
/// </summary>
/// <param name="sql">The SQL to split.</param>
/// <param name="parts">The SQL parts.</param>
/// <returns><c>True</c> if the SQL could be split; else, <c>False</c>.</returns>
public static bool SplitSql(string sql, out SqlParts parts)
{
parts.Sql = sql;
parts.SqlSelectRemoved = null;
parts.SqlCount = null;
parts.SqlOrderBy = null;
// Extract the columns from "SELECT <whatever> FROM"
var m = RegexColumns.Match(sql);
if (!m.Success)
{
return(false);
}
// Save column list and replace with COUNT(*)
var g = m.Groups[1];
parts.SqlSelectRemoved = sql.Substring(g.Index);
if (RegexDistinct.IsMatch(parts.SqlSelectRemoved))
{
parts.SqlCount = sql.Substring(0, g.Index) + "COUNT(" + m.Groups[1].ToString().Trim() + ") " + sql.Substring(g.Index + g.Length);
}
else
{
parts.SqlCount = sql.Substring(0, g.Index) + "COUNT(*) " + sql.Substring(g.Index + g.Length);
}
// Look for the last "ORDER BY <whatever>" clause not part of a ROW_NUMBER expression
m = SimpleRegexOrderBy.Match(parts.SqlCount);
if (m.Success)
{
g = m.Groups[0];
parts.SqlOrderBy = g + parts.SqlCount.Substring(g.Index + g.Length);
parts.SqlCount = parts.SqlCount.Substring(0, g.Index);
}
return(true);
}
