关于sql server：基于SQL集的范围

SELECT SUM(n) FROM numbers WHERE n BETWEEN 100 AND 200

WITH
  digits AS  -- Limit recursion by just using it for digits.
    (SELECT
      LEVEL - 1 AS num
    FROM
      DUAL
    WHERE
      LEVEL < 10
    CONNECT BY
      num = (PRIOR num) + 1),
  numrange AS
    (SELECT
      ones.num
        + (tens.num * 10)
        + (hundreds.num * 100)
        AS num
    FROM
      digits ones
      CROSS JOIN
        digits tens
      CROSS JOIN
        digits hundreds
    WHERE
      hundreds.num IN (1, 2)) -- Use the WHERE clause to restrict each digit as needed.
SELECT
  -- Some columns and operations
FROM
  numrange
  -- Join to other data if needed

SELECT n FROM dbo.Range(1, 11, 2) -- returns odd integers 1 to 11
SELECT n FROM dbo.RangeF(3.1, 3.5, 0.1) -- returns 3.1, 3.2, 3.3 and 3.4, but not 3.5 because of float inprecision. !fault(this)

USING System;
USING System.Data.SqlTypes;
USING Microsoft.SqlServer.Server;
USING System.Collections;

[assembly: CLSCompliant(TRUE)]
namespace Range {
    public static partial class UserDefinedFunctions {
        [Microsoft.SqlServer.Server.SqlFunction(DataAccess = DataAccessKind.None, IsDeterministic = TRUE, SystemDataAccess = SystemDataAccessKind.None, IsPrecise = TRUE, FillRowMethodName ="FillRow", TableDefinition ="n bigint")]
        public static IEnumerable Range(SqlInt64 START, SqlInt64 END, SqlInt64 incr) {
            RETURN NEW Ranger(START.Value, END.Value, incr.Value);
        }

        [Microsoft.SqlServer.Server.SqlFunction(DataAccess = DataAccessKind.None, IsDeterministic = TRUE, SystemDataAccess = SystemDataAccessKind.None, IsPrecise = TRUE, FillRowMethodName ="FillRowF", TableDefinition ="n float")]
        public static IEnumerable RangeF(SqlDouble START, SqlDouble END, SqlDouble incr) {
            RETURN NEW RangerF(START.Value, END.Value, incr.Value);
        }

        public static void FillRow(object ROW, OUT SqlInt64 n) {
            n =  NEW SqlInt64((long)ROW);
        }

        public static void FillRowF(object ROW, OUT SqlDouble n) {
            n = NEW SqlDouble((DOUBLE)ROW);
        }
    }

    internal class Ranger : IEnumerable {
        Int64 _start, _end, _incr;

        public Ranger(Int64 START, Int64 END, Int64 incr) {
            _start = START; _end = END; _incr = incr;
        }

        public IEnumerator GetEnumerator() {
            RETURN NEW RangerEnum(_start, _end, _incr);
        }
    }

    internal class RangerF : IEnumerable {
        DOUBLE _start, _end, _incr;

        public RangerF(DOUBLE START, DOUBLE END, DOUBLE incr) {
            _start = START; _end = END; _incr = incr;
        }

        public IEnumerator GetEnumerator() {
            RETURN NEW RangerFEnum(_start, _end, _incr);
        }
    }

    internal class RangerEnum : IEnumerator {
        Int64 _cur, _start, _end, _incr;
        bool hasFetched = FALSE;

        public RangerEnum(Int64 START, Int64 END, Int64 incr) {
            _start = _cur = START; _end = END; _incr = incr;
            IF ((_start < _end ^ _incr > 0) || _incr == 0)
                throw NEW ArgumentException("Will never reach end!");
        }

        public long CURRENT {
            GET { hasFetched = TRUE; RETURN _cur; }
        }

        object IEnumerator.Current {
            GET { hasFetched = TRUE; RETURN _cur; }
        }

        public bool MoveNext() {
            IF (hasFetched) _cur += _incr;
            RETURN (_cur > _end ^ _incr > 0);
        }

        public void Reset() {
            _cur = _start; hasFetched = FALSE;
        }
    }

    internal class RangerFEnum : IEnumerator {
        DOUBLE _cur, _start, _end, _incr;
        bool hasFetched = FALSE;

        public RangerFEnum(DOUBLE START, DOUBLE END, DOUBLE incr) {
            _start = _cur = START; _end = END; _incr = incr;
            IF ((_start < _end ^ _incr > 0) || _incr == 0)
                throw NEW ArgumentException("Will never reach end!");
        }

        public DOUBLE CURRENT {
            GET { hasFetched = TRUE; RETURN _cur; }
        }

        object IEnumerator.Current {
            GET { hasFetched = TRUE; RETURN _cur; }
        }

        public bool MoveNext() {
            IF (hasFetched) _cur += _incr;
            RETURN (_cur > _end ^ _incr > 0);
        }

        public void Reset() {
            _cur = _start; hasFetched = FALSE;
        }
    }
}

CREATE assembly Range FROM 'Range.dll' WITH permission_set=safe -- mod path to point to actual dll location on disk.
GO
CREATE FUNCTION dbo.Range(@START BIGINT, @END BIGINT, @incr BIGINT)
  RETURNS TABLE(n BIGINT)
  AS external name [Range].[Range.UserDefinedFunctions].[Range]
GO
CREATE FUNCTION dbo.RangeF(@START FLOAT, @END FLOAT, @incr FLOAT)
  RETURNS TABLE(n FLOAT)
  AS external name [Range].[Range.UserDefinedFunctions].[RangeF]
GO

WITH CTE AS
(
    SELECT 100 AS n
    UNION ALL
    SELECT n + 1 AS n FROM CTE WHERE n + 1 <= 200
)
SELECT n FROM CTE

SELECT SUM(numberGenerator.rank)
FROM
(
SELECT
    rank =  ( SELECT COUNT(*)  
              FROM reallyLargeTable t1
              WHERE t1.uniqueValue > t2.uniqueValue ),
    t2.uniqueValue id1,
    t2.uniqueValue id2
FROM reallyLargeTable t2
) numberGenerator
WHERE rank BETWEEN 1 AND 10