outer apply(select stuff((select '*'+rtrim(i.r) from cte_n i where i.r<=isnull((nullif(n.r-m.r,0)),1) for xml path('')),1,1,'') as v
参照：

效果图：
go
from cte_n n

以下是完整的SQL代码：
where n.r <=@rows
@x int=1,@y int=1,@sql nvarchar(max),@cols int

exec(@sql)
cross apply(select 'select '+stuff((select ',rtrim('+isnull(F1.v+'/(('+F2.v+')*'+F3.v+')','''''') +') as '+quotename(isnull(nullif((m.r +(@rows-n.r)+(m.r-1)*1)%@cols,0),@cols))
use tempdb
outer apply(select stuff((select '*'+rtrim(i.r) from cte_n i where i.r<=isnull((nullif(n.r-1,0)),1) for xml path('')),1,1,'') as v
/*
where m.r<@rows*2
),1,1,'') as data_lse
from cte_n m

(
outer apply(select stuff((select '*'+rtrim(i.r) from cte_n i where i.r<=isnull((nullif(m.r-1,0)),1) for xml path('')),1,1,'') as v
,cte_1 as(select n.r,b.data_lse
)b


select r from (select row_number() over(order by a.object_id) as r from sys.all_columns a ) x where r<=@rows*2
for xml path('')
)

select @sql=isnull(@sql+' union all ','')+data_lse from cte_1
set @cols=@rows*2-1
) F3
*/

无意中在csdn上看到一帖有关绘制杨辉三角的sql表达式，感觉很有意思。后来自己想下不借助临时表，根据杨辉三角的组合数计算方法C(n,m)=n!/[m!(n-m)!]，进行绘制。

复制代码 代码如下: