無意中在csdn上看到一帖有關(guān)繪制楊輝三角的sql表達式，感覺很有意思。后來自己想下不借助臨時表，根據(jù)楊輝三角的組合數(shù)計算方法C(n,m)=n!/[m!(n-m)!]，進行繪制。
use tempdb
go
set nocount on
declare @rows int=10, --行數(shù)，根據(jù)實際來控制
@x int=1,@y int=1,@sql nvarchar(max),@cols int
/*
根據(jù)楊輝三角的組合數(shù)計算方法：C(n,m)=n!/[m!(n-m)!]進行繪制
參照：http://baike.baidu.com/view/7804.htm
*/
set @cols=@rows*2-1
;with cte_n as
(
select r from (select row_number() over(order by a.object_id) as r from sys.all_columns a ) x where r=@rows*2
)
,cte_1 as(select n.r,b.data_lse
from cte_n n
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))
from cte_n m
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
) F1
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
) F2
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
) F3
where m.r@rows*2
order by isnull(nullif((m.r +(@rows-n.r)+(m.r-1)*1)%@cols,0),@cols) asc
for xml path('')
),1,1,'') as data_lse
)b
where n.r =@rows
)
select @sql=isnull(@sql+' union all ','')+data_lse from cte_1
exec(@sql)
