今天来个杨辉三角

[anzhiqiang_touzi]

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)

[zkyEric]

运行了一下,OK!!!

[yunzhongyouyun3615]

楼主好牛！！！有时间交流一下！！

[jacharyin]

经典题目哈

[coclocq]

哇，楼主犀利，学生来观摩学习下

[flycode310]

怎么没有截图呀

[JIACISCO]

强大！好好学习下了！

[visioncoder]

童鞋们，你们知道回字的四种写法吗？

[珞珈]

不是很理解其中的意思。还的好好学习呀

[WarOfTheKing]

我曾经用java打了杨辉三角的，比较简单，sql还没试过。

[大海上飞翔]

不佩服不行呀！sql技巧很高呀

[yxhshuaiman]

看来确实流行写这个 现在是写杨辉 下次写下华罗庚吧！

[zx6121390]

让我想起来刚学java的时候写的乘法口诀表了~！正的反的~！

[anzhiqiang_touzi]

今天心情好来我来顶顶

[秀HAPPY]

满好玩滴！牛人啊！

[xiaoxiangqing]

不错，学习下

[---涛声依旧---]

牛！

[Andy-W]

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 #

[hokecoko]

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

[无涯大者]

杨辉三角。学习

[yanian]