用T-SQL生成乘法表

Question

我试图在Microsoft SQL Server 2012中使用T-SQL生成乘法表，但遇到了宽度大于高度的情况。只要它不成立，一切顺利，但任何时候高度较大，所有索引大于上一个高度值的单元格都为空…为什么会这样呢?我该如何克服呢？

根据我的理解，它只是生成最高值的方阵，但我不太确定如何修复它……

DECLARE @InitialValue int, @Height int, @Width int, @ColumnNames varchar(max), @RowNames varchar(max), @sql varchar(max);
SET @InitialValue = 2;
SET @Height = 2
SET @Width = 5

SELECT
    @RowNames = COALESCE(@RowNames + ', ', '')
              + '[' + CAST(@InitialValue + number AS varchar) + ']'
FROM master..spt_values
WHERE type = 'P'
  AND number BETWEEN 0 AND @Height-2;

SELECT
    @ColumnNames = COALESCE(@ColumnNames + ', ', '')
              + '[' + CAST(@InitialValue + number AS varchar) + ']'
FROM master..spt_values
WHERE type = 'P'
  AND number BETWEEN 0 AND @Width-2;


SET @sql =
'WITH numbers AS (
  SELECT ' + CAST(@InitialValue AS varchar) + ' + number AS X
  FROM master..spt_values
  WHERE type = ''P''
    AND number BETWEEN 0 AND ' + CAST(@Height-2 AS varchar) +'
),
products AS (
  SELECT
    n1.X,
    PivotN = n2.X,
    P = n1.X * n2.X
  FROM numbers n1
    CROSS JOIN numbers n2
)
SELECT
  X, ' + @ColumnNames + '
FROM products
PIVOT (MAX(P) FOR PivotN IN (' + @ColumnNames + ')) p';
EXEC(@sql);

输出：

X   2   3   4   5
2   4   NULL    NULL    NULL

Answer 1

我并没有真正看过你的代码，我只是写了我自己的代码，它适用于任何高度和宽度。看看这个：

DECLARE @InitialValue INT = 1,
        @Height INT = 1,
        @Width INT  = 5,
        @PivotColumns VARCHAR(MAX);
IF OBJECT_ID('tempdb..##NumsTable') IS NOT NULL
    DROP TABLE ##numsTable;

CREATE TABLE ##NumsTable (num INT PRIMARY KEY);

WITH CTE_Nums
AS
(
    SELECT @InitialValue AS num
    UNION ALL
    SELECT num + 1
    FROM CTE_Nums
    WHERE num <=        CASE
                        WHEN @Height > @Width
                            THEN @Height
                        ELSE @Width
                    END
)

INSERT INTO ##numsTable
    SELECT num
    FROM CTE_Nums
    OPTION (MAXRECURSION 0)

SELECT @PivotColumns = COALESCE(@PivotColumns + ',','') + QUOTENAME(num)
FROM ##numsTable
WHERE num < @InitialValue + @Width;

DECLARE @SQL NVARCHAR(MAX);
SELECT @SQL = 
N'WITH CTE_crossJoin
AS
(
SELECT  A.num           AS rowNums,
        B.num           AS colNums,
        A.num * B.num   AS result
FROM ##numsTable A
CROSS JOIN ##numsTable B
WHERE       A.num < @IV + @H
        AND B.num < @IV+ @W
)

SELECT *
FROM CTE_crossJoin
PIVOT
(
    MAX(result) FOR colNums IN (' + @pivotColumns + ')
) pvt'

EXECUTE sp_executesql @sql,N'@IV INT,@H INT,@W INT', @IV = @initialValue,@H = @Height,@W = @Width

Answer 2

下面是一个不同的版本：

declare @InitialValue int = 2, @Height int = 10, @Width int = 10;
declare @cols varchar(4000), @f varchar(4000), @s varchar(4000);

with cols as(select @InitialValue as w
            union all 
            select w + 1 from cols where w < @InitialValue + @Width - 1)

select 
      @cols = STUFF((SELECT '],[' + convert(varchar(10), w) FROM cols FOR XML PATH ('')) , 1, 2, '') + ']',
      @f = 'case when h.h = 1 then ''X'' else cast(h.h as char(10)) end,' + 
              STUFF((SELECT '],h.h*s.[' + convert(varchar(10), w) FROM cols FOR XML PATH ('')) , 1, 2, '') + ']'

set @s = 'with height as (
            select 1 as h
            union all 
            select h + 1 from height where h < ' + CAST(@Height as varchar(10)) +           
            '),
width as(select ' + CAST(@InitialValue as varchar(10)) + ' as w
            union all 
            select w + 1 from width where w < ' + CAST(@InitialValue + @Width - 1 as varchar(10)) +'),
spread as(select * from width pivot(max(w) for w in(' + @cols + ')) p)                      
select ' + @f + ' 
from height h
cross join spread s'

print (@s)
exec (@s)

输出：

@InitialValue int = 2，@Height int = 10，@Width int = 10

X   2   3   4   5   6   7   8   9   10
2   4   6   8   10  12  14  16  18  20
3   6   9   12  15  18  21  24  27  30
4   8   12  16  20  24  28  32  36  40
5   10  15  20  25  30  35  40  45  50
6   12  18  24  30  36  42  48  54  60
7   14  21  28  35  42  49  56  63  70
8   16  24  32  40  48  56  64  72  80
9   18  27  36  45  54  63  72  81  90
10  20  30  40  50  60  70  80  90  100

@InitialValue int = 2，@Height int = 3，@Width int = 5

X   2   3   4   5
2   4   6   8   10
3   6   9   12  15

@InitialValue int = 5，@Height int = 4，@Width int = 3

X   5   6
2   10  12
3   15  18
4   20  24