/// <summary>
/// 为指定的原始查询生成指定分页效果的新查询。
/// </summary>
/// <param name="raw">原始查询</param>
/// <param name="pagingInfo">分页信息。</param>
/// <returns></returns>
protected override ISqlSelect ModifyToPagingTree(SqlSelect raw, PagingInfo pagingInfo)
{
if (PagingInfo.IsNullOrEmpty(pagingInfo))
{
throw new ArgumentNullException("pagingInfo");
}
if (!raw.HasOrdered())
{
throw new InvalidProgramException("必须排序后才能使用分页功能。");
}
var pageNumber = pagingInfo.PageNumber;
var pageSize = pagingInfo.PageSize;
var res = MakePagingTree(raw, pageNumber, pageSize);
return(res);
}
/// <summary>
/// 使用 ROWNUM 来进行分页。
/// </summary>
/// <param name="raw">The raw.</param>
/// <param name="pagingInfo">The paging information.</param>
/// <returns></returns>
/// <exception cref="System.ArgumentNullException">pagingInfo</exception>
/// <exception cref="System.InvalidProgramException">必须排序后才能使用分页功能。</exception>
protected override ISqlSelect ModifyToPagingTree(SqlSelect raw, PagingInfo pagingInfo)
{
if (PagingInfo.IsNullOrEmpty(pagingInfo))
{
throw new ArgumentNullException("pagingInfo");
}
if (!raw.HasOrdered())
{
throw new InvalidProgramException("必须排序后才能使用分页功能。");
}
var startRow = pagingInfo.PageSize * (pagingInfo.PageNumber - 1) + 1;
var endRow = startRow + pagingInfo.PageSize - 1;
var res = MakePagingTree(raw, startRow, endRow);
return(res);
}
/// <summary>
/// 为指定的原始查询生成指定分页效果的新查询。
/// </summary>
/// <param name="raw">原始查询</param>
/// <param name="pagingInfo">分页信息。</param>
/// <returns></returns>
private ISqlSelect ModifyToPagingTreeWithNotIn(SqlSelect raw, PagingInfo pagingInfo)
{
if (PagingInfo.IsNullOrEmpty(pagingInfo))
{
throw new ArgumentNullException("pagingInfo");
}
if (!raw.HasOrdered())
{
throw new InvalidProgramException("必须排序后才能使用分页功能。");
}
//如果是第一页，则只需要使用 TOP 语句即可。
if (pagingInfo.PageNumber == 1)
{
return(new SqlSelect
{
Selection = new SqlNodeList
{
new SqlLiteral {
FormattedSql = "TOP " + pagingInfo.PageSize + " "
},
raw.Selection ?? SqlSelectAll.Default
},
From = raw.From,
Where = raw.Where,
OrderBy = raw.OrderBy
});
}
/*********************** 代码块解释 *********************************
*
* 转换方案：
*
* SELECT *
* FROM ASN
* WHERE ASN.Id > 0
* ORDER BY ASN.AsnCode ASC
*
* 转换分页后：
*
* SELECT TOP 10 *
* FROM ASN
* WHERE ASN.Id > 0 AND ASN.Id NOT IN(
* SELECT TOP 20 Id
* FROM ASN
* WHERE ASN.Id > 0
* ORDER BY ASN.AsnCode ASC
* )
* ORDER BY ASN.AsnCode ASC
*
**********************************************************************/
//先要找到主表的 PK，分页时需要使用此主键列来生成分页 Sql。
//这里约定 Id 为主键列名。
var finder = new FirstTableFinder();
var pkTable = finder.Find(raw.From);
var pkColumn = new SqlColumn {
Table = pkTable, ColumnName = EntityConvention.IdColumnName
};
//先生成内部的 Select
var excludeSelect = new SqlSelect
{
Selection = new SqlNodeList
{
new SqlLiteral {
FormattedSql = "TOP " + (pagingInfo.PageNumber - 1) * pagingInfo.PageSize + " "
},
pkColumn
},
From = raw.From,
Where = raw.Where,
OrderBy = raw.OrderBy,
};
var res = new SqlSelect
{
Selection = new SqlNodeList
{
new SqlLiteral {
FormattedSql = "TOP " + pagingInfo.PageSize + " "
},
raw.Selection ?? SqlSelectAll.Default
},
From = raw.From,
OrderBy = raw.OrderBy,
};
var newWhere = new SqlColumnConstraint
{
Column = pkColumn,
Operator = SqlColumnConstraintOperator.NotIn,
Value = excludeSelect
};
if (raw.Where != null)
{
res.Where = new SqlBinaryConstraint
{
Left = raw.Where,
Opeartor = SqlBinaryConstraintType.And,
Right = newWhere
};
}
else
{
res.Where = newWhere;
}
return(res);
}
/// <summary>
/// 为指定的原始查询生成指定分页效果的新查询。
/// </summary>
/// <param name="raw">原始查询</param>
/// <param name="pagingInfo">分页信息。</param>
/// <returns></returns>
protected ISqlSelect ModifyToPagingTreeWithRowNumberOver(SqlSelect raw, PagingInfo pagingInfo)
{
if (PagingInfo.IsNullOrEmpty(pagingInfo))
{
throw new ArgumentNullException("pagingInfo");
}
if (!raw.HasOrdered())
{
throw new InvalidProgramException("必须排序后才能使用分页功能。");
}
//如果是第一页，则只需要使用 TOP 语句即可。
if (pagingInfo.PageNumber == 1)
{
return(new SqlSelect
{
Selection = new SqlNodeList
{
new SqlLiteral {
FormattedSql = "TOP " + pagingInfo.PageSize + " "
},
raw.Selection ?? SqlSelectAll.Default
},
From = raw.From,
Where = raw.Where,
OrderBy = raw.OrderBy
});
}
/*********************** 代码块解释 *********************************
*
* 转换方案：
*
* 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 newRaw = new SqlSelect
{
Selection = new SqlNodeList()
{
new SqlLiteral {
FormattedSql = "ROW_NUMBER() OVER ("
},
raw.OrderBy,
new SqlLiteral {
FormattedSql = ")_rowNumber,"
},
raw.Selection ?? SqlSelectAll.Default
},
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 = MakePagingTree(newRaw, startRow, endRow);
return(res);
}
/// <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);
}
/// <summary>
/// 为指定的原始查询生成指定分页效果的新查询。
/// </summary>
/// <param name="raw">原始查询</param>
/// <param name="pkColumn">需要指定主键列</param>
/// <param name="pagingInfo">分页信息。</param>
/// <returns></returns>
public virtual SqlSelect ModifyToPagingTree(SqlSelect raw, SqlColumn pkColumn, PagingInfo pagingInfo)
{
if (PagingInfo.IsNullOrEmpty(pagingInfo))
{
throw new ArgumentNullException("pagingInfo");
}
if (!raw.HasOrdered())
{
throw new InvalidProgramException("必须排序后才能使用分页功能。");
}
//如果是第一页，则只需要使用 TOP 语句即可。
if (pagingInfo.PageNumber == 1)
{
raw.Top = pagingInfo.PageSize;
return(raw);
}
/*********************** 代码块解释 *********************************
*
* 转换方案：
*
* SELECT *
* FROM ASN
* WHERE ASN.Id > 0
* ORDER BY ASN.AsnCode ASC
*
* SELECT TOP 10 *
* FROM ASN
* WHERE ASN.Id > 0 AND ASN.Id NOT IN(
* SELECT TOP 20 Id
* FROM ASN
* WHERE ASN.Id > 0
* ORDER BY ASN.AsnCode ASC
* )
* ORDER BY ASN.AsnCode ASC
*
**********************************************************************/
var excludeSelect = new SqlSelect
{
Top = (pagingInfo.PageNumber - 1) * pagingInfo.PageSize,
Selection = pkColumn,
From = raw.From,
Where = raw.Where,
OrderBy = raw.OrderBy,
};
var res = new SqlSelect
{
Top = pagingInfo.PageSize,
Selection = raw.Selection,
From = raw.From,
OrderBy = raw.OrderBy,
};
var newWhere = new SqlColumnConstraint
{
Column = pkColumn,
Operator = SqlColumnConstraintOperator.NotIn,
Value = excludeSelect
};
if (raw.Where != null)
{
res.Where = new SqlBinaryConstraint
{
Left = raw.Where,
Opeartor = SqlBinaryConstraintType.And,
Right = newWhere
};
}
else
{
res.Where = newWhere;
}
return(res);
}
/// <summary>
/// 为指定的原始查询生成指定分页效果的新查询。
/// </summary>
/// <param name="raw">原始查询</param>
/// <param name="pagingInfo">分页信息。</param>
/// <returns></returns>
/// <exception cref="System.ArgumentNullException">pagingInfo</exception>
/// <exception cref="System.InvalidProgramException">必须排序后才能使用分页功能。</exception>
protected virtual ISqlSelect ModifyToPagingTree(SqlSelect raw, PagingInfo pagingInfo)
{
if (PagingInfo.IsNullOrEmpty(pagingInfo))
{
throw new ArgumentNullException("pagingInfo");
}
if (!raw.HasOrdered())
{
throw new InvalidProgramException("必须排序后才能使用分页功能。");
}
/*********************** 代码块解释 *********************************
*
* 使用 ROW_NUMBER() 函数，此函数 SqlServer、Oracle 都可使用。
* 注意，这个方法只支持不太复杂 SQL 的转换。
*
* 源格式：
* select ...... from ...... order by xxxx asc, yyyy desc
* 不限于以上格式，只要满足没有复杂的嵌套查询，最外层是一个 Select 和 From 语句即可。
*
* 目标格式：
* select * from (select ......, row_number() over(order by xxxx asc, yyyy desc) _rowNumber from ......) x where x._rowNumber<10 and x._rowNumber>5;
**********************************************************************/
var startRow = pagingInfo.PageSize * (pagingInfo.PageNumber - 1) + 1;
var endRow = startRow + pagingInfo.PageSize - 1;
var innerSelect = new SqlSelect();
var selection = new SqlArray();
if (raw.Selection != null)
{
selection.Items.Add(raw.Selection);
}
selection.Items.Add(new SqlNodeList
{
new SqlLiteral {
FormattedSql = "row_number() over ("
},
raw.OrderBy,
new SqlLiteral {
FormattedSql = ") _rowNumber"
}
});
innerSelect.Selection = selection;
var subSelect = new SqlSubSelect
{
Select = innerSelect,
Alias = "x"
};
var rowNumberColumn = new SqlTree.SqlColumn
{
Table = subSelect,
ColumnName = "_rowNumber"
};
var pagingSelect = new SqlSelect();
pagingSelect.From = subSelect;
pagingSelect.Where = new SqlTree.SqlBinaryConstraint
{
Left = new SqlTree.SqlColumnConstraint
{
Column = rowNumberColumn,
Operator = SqlColumnConstraintOperator.GreaterEqual,
Value = startRow
},
Opeartor = SqlBinaryConstraintType.And,
Right = new SqlTree.SqlColumnConstraint
{
Column = rowNumberColumn,
Operator = SqlColumnConstraintOperator.LessEqual,
Value = endRow
}
};
return(pagingSelect);
}
