protected override SqlSubSelect VisitSqlSubSelect(SqlSubSelect sqlSelectRef)
{
_sql.Append("(");
_sql.AppendLine();
this.Indent++;
this.Visit(sqlSelectRef.Select);
this.Indent--;
_sql.AppendLine();
_sql.Append(")");
this.AppendNameCast();
_sql.Append(sqlSelectRef.Alias);
return sqlSelectRef;
}
/// <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;
}
protected virtual SqlSubSelect VisitSqlSubSelect(SqlSubSelect subSelect)
{
this.Visit(subSelect.Select);
return subSelect;
}
protected virtual SqlSubSelect VisitSqlSubSelect(SqlSubSelect subSelect)
{
this.Visit(subSelect.Select);
return(subSelect);
}
public void ORM_SqlTree_Select_SelectFromSelectResult_ORA()
{
var userTable = new SqlTable { TableName = "User" };
var subSelectRef = new SqlSubSelect
{
Select = new SqlSelect
{
Selection = new SqlSelectAll(),
From = userTable,
Where = new SqlColumnConstraint
{
Column = new SqlColumn { Table = userTable, ColumnName = "Id" },
Operator = SqlColumnConstraintOperator.Greater,
Value = 0
}
},
Alias = "T"
};
var select = new SqlSelect();
select.Selection = new SqlSelectAll();
select.From = subSelectRef;
select.Where = new SqlColumnConstraint
{
Column = new SqlColumn { Table = subSelectRef, ColumnName = "Id" },
Operator = SqlColumnConstraintOperator.Less,
Value = 100
};
var generator = new OracleSqlGenerator { AutoQuota = false };
generator.Generate(select);
var sql = generator.Sql;
Assert.IsTrue(sql.ToString() == @"SELECT *
FROM (
SELECT *
FROM USER
WHERE USER.ID > {0}
) T
WHERE T.ID < {1}");
}
