protected override SqlOrderByList VisitSqlOrderByList(SqlOrderByList sqlOrderByList)
{
_sql.Append("ORDER BY ");
for (int i = 0, c = sqlOrderByList.Items.Count; i < c; i++)
{
if (i > 0)
{
_sql.Append(", ");
}
this.Visit(sqlOrderByList.Items[i] as SqlOrderBy);
}
return(sqlOrderByList);
}
/// <summary>
/// 为指定的原始查询生成指定分页效果的新查询。
/// </summary>
/// <param name="raw">原始查询</param>
/// <param name="pagingInfo">分页信息。</param>
/// <returns></returns>
private ISqlSelect ModifyToPagingTree_With_RowNumber(SqlSelect raw, PagingInfo pagingInfo)
{
/*********************** 代码块解释 *********************************
*
* 转换方案：
* 使用 ROW_NUMBER() 函数。（此函数 SqlServer、Oracle 都可使用。）
*
* SELECT *
* FROM ASN
* WHERE ASN.Id > 0
* ORDER BY ASN.AsnCode ASC
*
* 转换分页后：
*
* SELECT * FROM
* (
* SELECT A.*, ROW_NUMBER() OVER (order by Id) _RowNumber
* FROM A
* ) T
* WHERE _RowNumber BETWEEN 1 AND 10
**********************************************************************/
var finder = new FirstTableFinder();
var pkTable = finder.Find(raw.From);
//在 Sql 分页的算法中，必须排序后才能使用分页功能，所以如果给定的 sql 中没有排序语句的话，则尝试使用任意表的一个默认的字段（Id）来进行排序。
var orderBy = raw.OrderBy;
if (!raw.HasOrdered())
{
if (SqlServerSqlGeneratorConfiguration.DefaultPagingSqlOrderbyColumn == null)
{
throw new InvalidProgramException("必须提供排序语句后，才能使用分页功能。");
}
orderBy = new SqlOrderByList
{
new SqlOrderBy
{
Column = new SqlColumn
{
Table = pkTable,
ColumnName = SqlServerSqlGeneratorConfiguration.DefaultPagingSqlOrderbyColumn
}
}
};
}
var newRaw = new SqlSelect
{
Selection = new SqlNodeList()
{
raw.Selection ?? new SqlSelectAll()
{
Table = pkTable
},
new SqlLiteral {
FormattedSql = ", ROW_NUMBER() OVER ("
},
orderBy,
new SqlLiteral {
FormattedSql = ") _RowNumber "
}
},
From = raw.From,
Where = raw.Where,
IsDistinct = raw.IsDistinct,
IsCounting = raw.IsCounting
};
var startRow = pagingInfo.PageSize * (pagingInfo.PageNumber - 1) + 1;
var endRow = startRow + pagingInfo.PageSize - 1;
var res = new SqlNodeList
{
new SqlLiteral(
@"SELECT * FROM
("),
newRaw,
new SqlLiteral(
@")T WHERE _RowNumber BETWEEN " + startRow + @" AND " + endRow)
};
return(res);
}
