Kentico WITH AllData AS 以及Between and

c:\workspace\company\kentico\source\kentico12\DataEngine\Database\Helpers\SqlHelper.cs

/// <summary>
/// Gets a nested query using WITH for further evaluation
/// </summary>
private static string GetWithQuery(string queryText)
{
var result = String.Format(
@"
WITH AllData AS
(
{0}
)
SELECT ##TOPN## ##COLUMNS##
FROM AllData
WHERE ##WHERE##
ORDER BY ##ORDERBY##
", queryText);

return result;
}

/// <summary>
/// Gets the condition matching values of the column between from and to
/// </summary>
/// <param name="column">Column name</param>
/// <param name="from">From value</param>
/// <param name="to">To value</param>
public static string GetBetween(string column, int from, int to)
{
return String.Format("{0} BETWEEN {1} AND {2}", column, from, to);
}

c:\workspace\company\kentico\source\kentico12\DataEngine\Database\Helpers\SqlHelper.cs

 public static string PreparePagedQuery(string queryText, QueryMacros macros, int offset, int maxRecords, bool getTotal = true, bool subQuery = false)
        {
            var columns = macros.Columns;

            // Build the query
            if (String.IsNullOrEmpty(columns))
            {
                columns = "*";
            }

            HandleEmptyColumns(ref columns);

            var orderBy = macros.OrderBy;

            HandleEmptyColumns(ref orderBy);

            // Add the system columns for paging
            if (!String.IsNullOrEmpty(orderBy))
            {
                if (macros.TopN <= 0)
                {
                    // Remove Order by if TopN is not specified (cause exceptions)
                    queryText = SqlMacroHelper.RemoveOrderBy(queryText);
                }

                var originalColumns = columns;

                // Append row number
                string rowNumber = new RowNumberColumn(SystemColumns.ROW_NUMBER, orderBy).ToString();
                columns = AddColumns(columns, rowNumber);

                // Build the query text
                var rowsWhere = GetBetween(SystemColumns.ROW_NUMBER, offset + 1, offset + maxRecords);

                var totalColumn = (getTotal ? GetTotalColumn(macros.Total, subQuery) : null);

                if (subQuery)
                {
                    // Get query as sub-query
                    var cols = AddColumns(originalColumns, totalColumn);

                    queryText = PrepareNestedQuery(queryText, rowsWhere, null, 0, cols);
                }
                else
                {
                    // Get query with CTE
                    queryText = PrepareWithQuery(queryText, rowsWhere, SystemColumns.ROW_NUMBER, 0, totalColumn);
                }

                orderBy = null;
            }

            // Update fields in expressions
            macros.OrderBy = orderBy;
            macros.Columns = columns;

            return queryText;
        }

c:\workspace\company\kentico\source\kentico12\DataEngine\Query\Data\DataQueryBase.cs

queryText = SqlHelper.PreparePagedQuery(queryText, expressions, settings.Offset, settings.MaxRecords, !settings.IsSubQuery, isInnerQuery);

 private string GenerateQueryText(DataQuerySettings settings)
        {
            string queryText;

            lock (lockObject)
            {
                // Prepare complete parameters
                if (settings == null)
                {
                    settings = GetCompleteSettings();
                }

                // Get base query text
                queryText = GetBaseQueryText();

                var parameters = settings.Parameters;
                var expressions = settings.GetExpressions(parameters);

                var isInnerQuery = settings.IsSubQuery || settings.IsNested;

                if (settings.IsPagedQuery)
                {
                    // Check if OrderBy is set
                    if (String.IsNullOrEmpty(expressions.OrderBy))
                    {
                        throw new NotSupportedException("[DataQuery.GenerateQueryText]: The paged query cannot be generated without the orderBy set.");
                    }

                    // Prepare the query
                    queryText = SqlHelper.PreparePagedQuery(queryText, expressions, settings.Offset, settings.MaxRecords, !settings.IsSubQuery, isInnerQuery);
                }

                // Prepare query and resolve macros
                if (parameters != null)
                {
                    queryText = isInnerQuery ? parameters.ResolveMacros(queryText) : parameters.GetCompleteQueryText(queryText);
                }

                queryText = expressions.ResolveMacros(queryText);
            }

            return queryText;
        }

c:\workspace\company\kentico\source\kentico12\DataEngine\Query\Execution\QueryParameters.cs

var queryText = SqlHelper.PreparePagedQuery(Text, macros, Offset, maxRecords, getTotal, subQuery);

  internal void FinalizeForExecution(bool getTotal = true, bool subQuery = false)
        {
            var maxRecords = MaxRecords;
            if (maxRecords > 0)
            {
                // Transform parameters to a paging query if paging is configured
                SqlHelper.CheckPagedQuery(this);

                // Prepare the query
                var macros = Macros;

                var queryText = SqlHelper.PreparePagedQuery(Text, macros, Offset, maxRecords, getTotal, subQuery);

                // Prepare the parameters and always allow cache for paged results (always is select)
                Text = queryText;
                Offset = 0;
                MaxRecords = 0;
            }

            IsForExecution = true;
        }