// 创建 SELECT 命令
protected override Command ParseSelectCommand <T>(DbQueryableInfo_Select <T> sQueryInfo, int indent, bool isOuter, ResolveToken token)
{
// 说明：
// 1.OFFSET 前必须要有 'ORDER BY'，即 'Skip' 子句前必须使用 'OrderBy' 子句
// 2.在有统计函数的<MAX,MIN...>情况下，如果有 'Distinct' 'GroupBy' 'Skip' 'Take' 子句，则需要使用嵌套查询
// 3.'Any' 子句将翻译成 IF EXISTS...
// 4.分组再分页时需要使用嵌套查询，此时子查询不需要 'OrderBy' 子句，但最外层则需要
// 5.'Skip' 'Take' 子句视为语义结束符，在其之后的子句将使用嵌套查询
// 6.导航属性中有 1:n 关系的，需要使用嵌套查询，否则分页查询会有问题
// 8.如果有分页，则使用嵌套
// 9.如果有分页还有OrderBy，则使用嵌套的嵌套
// 导航属性中有1:n关系，只统计主表
// 例：AccountList = a.Client.AccountList,
DbQueryableInfo_Select <T> subQuery = sQueryInfo.SubQueryInfo as DbQueryableInfo_Select <T>;
if (sQueryInfo.HasMany && subQuery != null && subQuery.StatisExpression != null)
{
sQueryInfo = subQuery;
}
bool useStatis = sQueryInfo.StatisExpression != null;
bool useNesting = sQueryInfo.HasDistinct || sQueryInfo.GroupByExpression != null || sQueryInfo.Skip > 0 || sQueryInfo.Take > 0;
string alias0 = token != null && !string.IsNullOrEmpty(token.TableAliasName) ? (token.TableAliasName + "0") : "t0";
// 没有统计函数或者使用 'Skip' 子句，则解析OrderBy
// 导航属性如果使用嵌套，除非有 TOP 或者 OFFSET 子句，否则不能用ORDER BY
bool useOrderBy = (!useStatis || sQueryInfo.Skip > 0) && !sQueryInfo.HasAny && (!sQueryInfo.SubQueryByMany || (sQueryInfo.Skip > 0 || sQueryInfo.Take > 0));
IDbQueryable dbQueryable = sQueryInfo.SourceQuery;
TableAliasCache aliases = this.PrepareAlias <T>(sQueryInfo, token);
MappingCommand cmd = new MappingCommand(this, aliases, token)
{
HasMany = sQueryInfo.HasMany
};
ISqlBuilder jf = cmd.JoinFragment;
ISqlBuilder wf = cmd.WhereFragment;
(jf as OracleSqlBuilder).IsOuter = isOuter;
jf.Indent = indent;
#region 嵌套查询
if (useStatis && useNesting)
{
// SELECT
jf.Append("SELECT ");
jf.AppendNewLine();
// SELECT COUNT(1)
var visitor2 = new StatisExpressionVisitor(this, aliases, sQueryInfo.StatisExpression, sQueryInfo.GroupByExpression, alias0);
visitor2.Write(jf);
cmd.AddNavMembers(visitor2.NavMembers);
// SELECT COUNT(1) FROM
jf.AppendNewLine();
jf.Append("FROM ( ");
indent += 1;
jf.Indent = indent;
(jf as OracleSqlBuilder).IsOuter = false;
}
#endregion
#region 择子句
// SELECT 子句
if (jf.Indent > 0)
{
jf.AppendNewLine();
}
jf.Append("SELECT ");
if (sQueryInfo.HasAny)
{
jf.Append("CASE WHEN COUNT(1) = 1 THEN 1 ELSE 0 END FROM (");
indent += 1;
jf.Indent = indent;
jf.AppendNewLine();
jf.Append("SELECT 1 ");
}
if (useStatis && !useNesting)
{
// 如果有统计函数，并且不是嵌套的话，则直接使用SELECT <MAX,MIN...>，不需要解析选择的字段
jf.AppendNewLine();
var visitor2 = new StatisExpressionVisitor(this, aliases, sQueryInfo.StatisExpression, sQueryInfo.GroupByExpression);
visitor2.Write(jf);
cmd.AddNavMembers(visitor2.NavMembers);
}
else
{
// DISTINCT 子句
if (sQueryInfo.HasDistinct)
{
jf.Append("DISTINCT ");
}
#region 择字段
if (!sQueryInfo.HasAny)
{
// SELECT 范围
ISqlBuilder sf = this.CreateSqlBuilder(token);
sf.Indent = jf.Indent + ((sQueryInfo.Skip > 0 || sQueryInfo.Take > 0) ? 2 : 0);
(sf as OracleSqlBuilder).IsOuter = (sQueryInfo.Skip > 0 || sQueryInfo.Take > 0) ? false : (jf as OracleSqlBuilder).IsOuter;
var visitor2 = new ColumnExpressionVisitor(this, aliases, sQueryInfo);
visitor2.Write(sf);
cmd.PickColumns = visitor2.PickColumns;
cmd.PickColumnText = visitor2.PickColumnText;
cmd.Navigations = visitor2.Navigations;
cmd.AddNavMembers(visitor2.NavMembers);
// 分页，产生两层嵌套
if (sQueryInfo.Skip > 0 || sQueryInfo.Take > 0)
{
// 第一层嵌套
int index = 0;
jf.AppendNewLine();
foreach (var column in cmd.PickColumns)
{
jf.AppendMember(alias0, column.Name);
jf.AppendAs(column.NewName);
index += 1;
if (index < cmd.PickColumns.Count)
{
jf.Append(',');
jf.AppendNewLine();
}
}
jf.AppendNewLine();
jf.Append("FROM(");
// 第二层嵌套
indent += 1;
isOuter = false;
jf.Indent = indent;
(jf as OracleSqlBuilder).IsOuter = isOuter;
jf.AppendNewLine();
jf.Append("SELECT");
jf.AppendNewLine();
foreach (var column in cmd.PickColumns)
{
jf.AppendMember(alias0, column.NewName);
jf.AppendAs(column.NewName);
jf.Append(',');
jf.AppendNewLine();
}
jf.Append("ROWNUM AS Row_Number0");
jf.AppendNewLine();
jf.Append("FROM(");
// 第三层嵌套
indent += 1;
jf.Indent = indent;
jf.AppendNewLine();
jf.Append("SELECT");
}
jf.Append(sf);
}
#endregion
}
#endregion
#region 顺序解析
// FROM 子句
jf.AppendNewLine();
jf.Append("FROM ");
if (sQueryInfo.SubQueryInfo != null)
{
// 子查询
jf.Append('(');
Command cmd2 = this.ParseSelectCommand <T>(sQueryInfo.SubQueryInfo as DbQueryableInfo_Select <T>, indent + 1, false, token);
jf.Append(cmd2.CommandText);
jf.AppendNewLine();
jf.Append(") ");
jf.Append(alias0);
jf.Append(' ');
}
else
{
var typeRuntime = TypeRuntimeInfoCache.GetRuntimeInfo(sQueryInfo.FromType);
jf.AppendMember(typeRuntime.TableName, !typeRuntime.IsTemporary);
jf.Append(' ');
jf.Append(alias0);
jf.Append(' ');
}
// LEFT<INNER> JOIN 子句
ExpressionVisitorBase visitor = new JoinExpressionVisitor(this, aliases, sQueryInfo.Joins);
visitor.Write(jf);
wf.Indent = jf.Indent;
// WHERE 子句
visitor = new WhereExpressionVisitor(this, aliases, sQueryInfo.WhereExpression);
visitor.Write(wf);
cmd.AddNavMembers(visitor.NavMembers);
// GROUP BY 子句
visitor = new GroupByExpressionVisitor(this, aliases, sQueryInfo.GroupByExpression);
visitor.Write(wf);
cmd.AddNavMembers(visitor.NavMembers);
// HAVING 子句
visitor = new HavingExpressionVisitor(this, aliases, sQueryInfo.HavingExpression, sQueryInfo.GroupByExpression);
visitor.Write(wf);
cmd.AddNavMembers(visitor.NavMembers);
// ORDER 子句
if (sQueryInfo.OrderBys.Count > 0 && useOrderBy)
{
visitor = new OrderByExpressionVisitor(this, aliases, sQueryInfo.OrderBys, sQueryInfo.GroupByExpression);
visitor.Write(wf);
cmd.AddNavMembers(visitor.NavMembers);
}
#endregion
#region 嵌套查询
if (useStatis && useNesting)
{
cmd.CombineFragments();
indent -= 1;
jf.Indent = indent;
jf.AppendNewLine();
jf.Append(") ");
jf.Append(alias0);
}
#endregion
#region 嵌套导航
// TODO Include 从表，没分页，OrderBy 报错
if (sQueryInfo.HasMany && subQuery != null && subQuery.OrderBys.Count > 0 && subQuery.StatisExpression == null && !(subQuery.Skip > 0 || subQuery.Take > 0))
{
// OrderBy("a.CloudServer.CloudServerName");
cmd.CombineFragments();
visitor = new OrderByExpressionVisitor(this, aliases, subQuery.OrderBys);//, null, "t0");
visitor.Write(jf);
}
#endregion
#region 并集查询
// UNION 子句
if (sQueryInfo.Unions != null && sQueryInfo.Unions.Count > 0)
{
cmd.CombineFragments();
for (int index = 0; index < sQueryInfo.Unions.Count; index++)
{
jf.AppendNewLine();
jf.Append("UNION ALL");
if (indent == 0)
{
jf.AppendNewLine();
}
Command cmd2 = this.ParseSelectCommand <T>(sQueryInfo.Unions[index] as DbQueryableInfo_Select <T>, indent, isOuter, token);
jf.Append(cmd2.CommandText);
}
}
#endregion
#region 分页查询
if (sQueryInfo.Take > 0 || sQueryInfo.Skip > 0)
{
// 合并 WHERE
cmd.CombineFragments();
indent -= 1;
jf.Indent = indent;
jf.AppendNewLine();
jf.Append(") ");
jf.Append(alias0);
indent -= 1;
jf.Indent = indent;
jf.AppendNewLine();
jf.Append(") ");
jf.Append(alias0);
jf.AppendNewLine();
jf.Append("WHERE ");
if (sQueryInfo.Skip > 0)
{
jf.AppendMember(alias0, "Row_Number0");
jf.Append(" > ");
jf.Append(this.DbValue.GetSqlValue(sQueryInfo.Skip, token));
}
if (sQueryInfo.Take > 0)
{
if (sQueryInfo.Skip > 0)
{
jf.Append(" AND ");
}
jf.AppendMember(alias0, "Row_Number0");
jf.Append(" <= ");
jf.Append(this.DbValue.GetSqlValue((sQueryInfo.Skip + sQueryInfo.Take), token));
}
}
#endregion
#region Any 子句
// 'Any' 子句
if (sQueryInfo.HasAny)
{
// 产生 WHERE 子句
cmd.CombineFragments();
// 如果没有分页，则显式指定只查一笔记录
if (sQueryInfo.Take == 0 && sQueryInfo.Skip == 0)
{
if (sQueryInfo.WhereExpression != null && sQueryInfo.WhereExpression.Expressions != null)
{
jf.Append(" AND ROWNUM <= 1");
}
else
{
jf.AppendNewLine();
jf.Append("WHERE ROWNUM <= 1");
}
}
indent -= 1;
jf.Indent = indent;
jf.AppendNewLine();
jf.Append(") ");
jf.Append(alias0);
}
#endregion
return(cmd);
}
