现在有一个表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.idSQL2000以上

DECLARE @idtb TABLE(id INT)

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

SET @sum = 0

WHILE @sum15

BEGIN

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

    FROM tb

    WHERE num15)

    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 行受影响)

*/