Ejemplo n.º 1
0
        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;
        }
Ejemplo n.º 2
0
        /// <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;
        }
Ejemplo n.º 3
0
 protected virtual SqlSubSelect VisitSqlSubSelect(SqlSubSelect subSelect)
 {
     this.Visit(subSelect.Select);
     return subSelect;
 }
Ejemplo n.º 4
0
 protected virtual SqlSubSelect VisitSqlSubSelect(SqlSubSelect subSelect)
 {
     this.Visit(subSelect.Select);
     return(subSelect);
 }
Ejemplo n.º 5
0
        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}");
        }