SQL杨辉三角

declare @SQL varchar(max) 
declare @INSERT varchar(max) 
declare @Update varchar(max) 
declare @n int
set @n=8

set @SQL='SELECT'
set @INSERT=''
set @Update=''

select @SQL=@SQL+C,@INSERT=@INSERT+I from 
(
    select case when number<>@n+1  Then

declare @SQL varchar(max) 
declare @INSERT varchar(max) 
declare @Update varchar(max) 
declare @n int
set @n=8

set @SQL='SELECT'
set @INSERT=''
set @Update=''

select @SQL=@SQL+C,@INSERT=@INSERT+I from 
(
    select case when number<>@n+1  Then ' NULL as ['+CAST(number as varchar)+'],' else ' 1 as ['+CAST(number as varchar)+'],' end as C,
    case when (@n+1-number)>0 Then ' INSERT INTO #t(['+CAST(@n+1-number as varchar)+'],['+CAST(@n+1+number as varchar)+'],ID) VALUES(1,1,'+CAST(number+1 as varchar)+')' else '' end as I
    from master..spt_values where type='P' and number BETWEEN 1 and @n*2+1
)a

select @Update=@Update+' Update #T set '+
(
    select '['+CAST(number as varchar)+']=(select top 1 (CAST(['+CAST(number-1 as varchar)+'] as int)+CAST(['+CAST(number+1 as varchar)+'] as int))   from #t a where a.ID=#t.ID-1),'+''
    from master..spt_values a where type='P' and number BETWEEN aa.Min and aa.Max  order by number desc for xml path('')
)+' ID=ID where ID='+CAST(number as varchar)
from 
(
    select number+3 as number,
    case when (@n+1-number)>0 Then @n+1-number else '' end as Min,
    case when (@n+1-number)>0 Then @n+1+number else '' end as Max
    from master..spt_values where type='P' and number+3  BETWEEN 0 and @n+2-1
)aa
set @SQL=@SQL+' 1 as ID INTO #t alter table #t alter column ['+CAST(@n+1 as varchar)+']  Int '+@INSERT+' '+@Update+' select * from #t order by ID'
exec(@SQL)

　　

' NULL as ['+CAST(number as varchar)+'],' else ' 1 as ['+CAST(number as varchar)+'],' end as C, case when (@n+1-number)>0 Then ' INSERT INTO #t(['+CAST(@n+1-number as varchar)+'],['+CAST(@n+1+number as varchar)+'],ID) VALUES(1,1,'+CAST(number+1 as varchar)+')' else '' end as I from master..spt_values where type='P' and number BETWEEN 1 and @n*2+1 )a select @Update=@Update+' Update #T set '+ ( select '['+CAST(number as varchar)+']=(select top 1 (CAST(['+CAST(number-1 as varchar)+'] as int)+CAST(['+CAST(number+1 as varchar)+'] as int)) from #t a where a.ID=#t.ID-1),'+'' from master..spt_values a where type='P' and number BETWEEN aa.Min and aa.Max order by number desc for xml path('') )+' ID=ID where ID='+CAST(number as varchar) from ( select number+3 as number, case when (@n+1-number)>0 Then @n+1-number else '' end as Min, case when (@n+1-number)>0 Then @n+1+number else '' end as Max from master..spt_values where type='P' and number+3 BETWEEN 0 and @n+2-1 )aa set @SQL=@SQL+' 1 as ID INTO #t alter table #t alter column ['+CAST(@n+1 as varchar)+'] Int '+@INSERT+' '+@Update+' select * from #t order by ID' exec(@SQL)

　　

转载于:https://www.cnblogs.com/laojiefang/archive/2012/06/03/2532498.html