一列数字随机求和等于某数的算法(整理)

现在有一个表Table1进行随机查询, 检索结果记录条数不限, 但是要求Num求和结果为一个固定值,如结果为15

ID    NUM
1    5
2    3
3    2
4    5
5    4
6    5
7    3
8    2
9    4
10    3

 

IF OBJECT_ID('[tb]') IS NOT NULL

    DROP TABLE [tb]

GO

CREATE TABLE [tb]

(

    [ID] INT, [Num] INT

)

INSERT [tb]

SELECT 1, 5 UNION ALL

SELECT 2, 3 UNION ALL

SELECT 3, 2 UNION ALL

SELECT 4, 5 UNION ALL

SELECT 5, 4 UNION ALL

SELECT 6, 5 UNION ALL

SELECT 7, 3 UNION ALL

SELECT 8, 2 UNION ALL

SELECT 9, 4 UNION ALL

SELECT 10, 3

GO

--select * from [tb]

 

-->SQL2005以上

;WITH t AS

(

    SELECT *, PATH = CAST(id AS VARCHAR), total = num

    FROM tb

    UNION ALL

    SELECT b.id, b.num, CAST(a.path+'-'+RTRIM(b.id) AS VARCHAR), a.total+b.num

    FROM t a

    JOIN tb b

        ON  a.id<b.id

            AND a.total+b.num <= 15

)

SELECT id, num

FROM tb, (

         SELECT TOP 1 PATH

         FROM t

         WHERE total = 15

         ORDER BY NEWID()

     ) a

WHERE CHARINDEX('-'+RTRIM(id)+'-', '-'+PATH+'-')>0

/*

id          num

----------- -----------

3           2

4           5

6           5

7           3

 

(4 行受影响)

*/

 

-->SQL2000以上

DECLARE @idtb TABLE(id INT)

DECLARE @num INT, @id INT, @sum  INT

SET @sum = 0

WHILE @sum<>15

BEGIN

    SELECT TOP 1 @id = id, @num = num

    FROM tb

    WHERE num<= 15

    ORDER BY NEWID()

    IF @num=15

        INSERT INTO @idtb

        SELECT @id

    ELSE

    IF NOT EXISTS(

           SELECT 1

           FROM @idtb

           WHERE id = @id

       )

        INSERT INTO @idtb

        SELECT @id

    SELECT @sum = SUM(num)

    FROM tb

    WHERE id IN (

    SELECT id

        FROM @idtb

       )

    IF (@sum>15)

    BEGIN

        DELETE @idtb

    END

END

 

SELECT *

FROM tb

WHERE id IN (

    SELECT id

    FROM @idtb

    )

/*

ID          Num

----------- -----------

3           2

4           5

6           5

10          3

 

(4 行受影响)

*/

 

http://topic.youkuaiyun.com/u/20090421/12/5113c4ab-4631-4dc9-be9f-e6d88f0526d8.html