今天来个杨辉三角

anzhiqiang_touzi 2012-06-01 06:00:13

加精





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)

...全文

6544 127 打赏收藏转发到动态举报

写回复

用AI写文章

127 条回复

切换为时间正序

请发表友善的回复…

发表回复

yanian 2014-04-07

打赏
举报

无涯大者 2013-04-06

打赏
举报

杨辉三角。学习

hokecoko 2013-04-06

打赏
举报

好强大啊，第一次知道sql还可以这么玩，学习了

Andy-W 2013-04-03

打赏
举报

use tempdb

go

set nocount on

declare @n int=10, --行数，根据实际来控制

		@x int=1,@y int=1,@sql nvarchar(1000)=''



/*

绘制方法：先根据行数@n,构造表格#，再根据杨辉三角形的组合数性质（第n行的第m个数和第n-m+1个数相等，即C(n-1,m-1)=C(n-1,n-m) ）绘制结果数据

参照：http://baike.baidu.com/view/7804.htm

*/



if object_id('#') is not null drop table #

create table #([1] nvarchar(50) not null default(''))



while @x<@n*2-1

begin

	set @x+=1

	set @sql='alter table # add '+quotename(@x)+' nvarchar(50) not null default('''');'

	exec( @sql)	



	if @x>((@n*2.-1)/2)

	begin

		set @sql='insert into #('+quotename(@x)+') values(1)'

		exec(@sql)

		set @y=1

		while @y<=( @x-@n)

		begin

			set @sql='update a set '+quotename(@x-@y*2)+'='+

				case when @x-@y*2-1=0 then '0' else 'convert(int,b.'+quotename(@x-@y*2-1)+')' end +'+'+

				case when @x-@y*2+1=@n*2.-1 then '0' else 'convert(int,b.'+quotename(@x-@y*2+1)+')' end +' from # a join # b on b.'+quotename(@x-1)+'=1 where a.'+quotename(@x)+'=1' 

			exec(@sql)

			set @y+=1

		end

	end

end

select * from #