三角乘法矩阵生成 [英] Triangular Multiplication Matrix generation

问题描述

尝试在 T-SQL 中生成三角乘法矩阵-就像一个三角乘法矩阵看起来像这样:

<前>00 10 2 40 3 6 90 4 8 12 16

我一直无法为此找到有效的解决方案.任何帮助表示赞赏.

解决方案

使用 XML(SQL 2005 及更高版本)有一个聪明的方法来做到这一点:

with Nums(n) as (选择 0 联合所有选择 1 个联合全部select 2 union allselect 3 union allselect 4 union all选择 5 联合所有select 6 union all选择 7 -- 根据需要调整大小或使其永久化)从 Nums 中选择 x 作为 Prod交叉申请(选择 Cast(Nums.n*(N2.n) as varchar(80))+space(3-Len(Nums.n*(N2.n))-- 如果需要，扩展 varchar 大小作为 [文本()]从 Nums 作为 N2其中 Nums.n >= n按 N2.n 排序对于 xml 路径('')) 作为 X(x)其中 n <= 4 -- 根据需要进行调整按 n 排序；

(一个永久的 Nums 表是个好主意.)

输出是这样的:

产品--------00 10 2 40 3 6 90 4 8 12 16

Trying to generate a Triangular Multiplication Matrix IN T-SQL- Like a triangular multiplication matrix will look like this:

0
0 1
0 2 4
0 3 6 9
0 4 8 12 16

I have not been able to find an efficient solution for this. Any help is appreciated.

解决方案

There's a clever way to do this with XML (SQL 2005 and later):

with Nums(n) as (
  select 0 union all
  select 1 union all
  select 2 union all
  select 3 union all
  select 4 union all
  select 5 union all
  select 6 union all
  select 7 -- resize as needed or make it permanent
)
  select x as Prod from Nums
  cross apply (
    select Cast(Nums.n*(N2.n) as varchar(80))+space(3-Len(Nums.n*N2.n))
    -- expand the varchar size if needed
    as [text()]
    from Nums as N2
    where Nums.n >= n 
    order by N2.n
    for xml path('')
  ) as X(x)
  where n <= 4 -- Adjust as needed
  order by n;

(A permanent Nums table is a good idea.)

The output is this:

Prod
--------
0  
0  1  
0  2  4  
0  3  6  9  
0  4  8  12 16 

这篇关于三角乘法矩阵生成的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！