數(shù)據(jù)庫環(huán)境：SQL SERVER2008R2

今天我們用SQL實(shí)現(xiàn)一下九九乘法表的功能。

實(shí)現(xiàn)的邏輯不是很復(fù)雜，難點(diǎn)在于怎么把想要的內(nèi)容從同一列里頭拼接到同一行上。

在這里，我們用到了FOR XML PATH，F(xiàn)OR XML的用法，可以到MSDN去看，這里就不細(xì)講了。

/*數(shù)據(jù)準(zhǔn)備,生成自然數(shù)1-9*/WITH    x0          AS ( SELECT   id               FROM     t100               WHERE    id <= 9             ),/*生成所有可能的乘法組合*/        x1          AS ( SELECT   a.id AS aid ,                        b.id AS bid ,                        CONVERT(VARCHAR(1), a.id) + ' * '                        + CONVERT(VARCHAR(1), b.id) + ' = '                        + CONVERT(VARCHAR(2), a.id * b.id) AS result               FROM     x0 a ,                        x0 b               WHERE    a.id <= b.id             )    /*把被乘數(shù)相同的轉(zhuǎn)到同一行上*/    SELECT  result    FROM    ( SELECT    bid ,                        ( SELECT    '  ' + result AS [text()]                          FROM      x1 AS t2                          WHERE     t2.bid = t1.bid                        FOR                          XML PATH('')                        ) AS result              FROM      x1 AS t1              GROUP BY  bid            ) t;    

相關(guān)的說明已經(jīng)在代碼里進(jìn)行注釋。最后，附上結(jié)果圖。