SQL 按表中的一个int值拆分成对应的个数的记录条数

IF OBJECT_ID(N'tempdb..#tmp') IS NOT NULL

    DROP TABLE #tmp

   

CREATE TABLE #tmp ( aaa VARCHAR(10), bbb INT )

 

INSERT  INTO #tmp

        ( aaa, bbb )

VALUES  ( '11111', 5 )

INSERT  INTO #tmp

        ( aaa, bbb )

VALUES  ( '22222', 3 )

INSERT  INTO #tmp

        ( aaa, bbb )

VALUES  ( '33333', 2 )

INSERT  INTO #tmp

        ( aaa, bbb )

VALUES  ( '44444', 1 )

INSERT  INTO #tmp

        ( aaa, bbb )

VALUES  ( '55555', 3 )

 

--SELECT * FROM #tmp

 

;WITH    Digits

          AS ( SELECT   0 AS Number

UNION SELECT 1

UNION SELECT 2

UNION SELECT 3

UNION SELECT 4

UNION SELECT 5

UNION SELECT 6

UNION SELECT 7

UNION SELECT 8

UNION SELECT 9

             )

    SELECT  b.aaa ,

            b.bbb ,

            T.number

    FROM    #tmp b

            JOIN ( SELECT   ( d5.Number * 100000 ) + ( d4.Number * 10000 )

                            + ( d3.Number * 1000 ) + ( d2.Number * 100 )

                            + ( d1.Number * 10 ) + d0.Number AS Number

                   FROM     Digits AS d0 ,

                            Digits AS d1 ,

                            Digits AS d2 ,

                            Digits AS d3 ,

                            Digits AS d4 ,

                            Digits AS d5

                 ) t ON 1 = 1

    WHERE  t.number <= b.bbb

    ORDER BY b.aaa ,

            b.bbb ,

            t.Number