接活跃气氛贴：9*9问题
原帖：http://topic.csdn.net/u/20120526/13/11dbdbd6-c869-4078-8872-427b89ce6f6c.html
- SQL code
--强大的pivot--数据准备create table t4( row int, col int, val char(10))--添加测试数据declare @x intdeclare @y intdeclare @c varchar(6000)set @x = 1while(@x<=9)begin select @y=@x,@c='' while(@y<=9) begin select @c=cast(@x as varchar)+'x'+cast(@y as varchar)+'=' +(case when len(ltrim(@x*@y))>1 then '' else ' ' end)+ltrim(@x*@y)+' ' select @y=@y+1 insert into t4 values(@y-1,@x,@c) end select @x=@x+1end---------------------------------------------row col val----------- ----------- ----------1 1 1x1= 1 2 1 1x2= 2 3 1 1x3= 3 4 1 1x4= 4 5 1 1x5= 5 6 1 1x6= 6 7 1 1x7= 7 8 1 1x8= 8 9 1 1x9= 9 2 2 2x2= 4 3 2 2x3= 6 4 2 2x4= 8 5 2 2x5=10 6 2 2x6=12 7 2 2x7=14 8 2 2x8=16 9 2 2x9=18 3 3 3x3= 9 4 3 3x4=12 5 3 3x5=15 6 3 3x6=18 7 3 3x7=21 8 3 3x8=24 9 3 3x9=27 4 4 4x4=16 5 4 4x5=20 6 4 4x6=24 7 4 4x7=28 8 4 4x8=32 9 4 4x9=36 5 5 5x5=25 6 5 5x6=30 7 5 5x7=35 8 5 5x8=40 9 5 5x9=45 6 6 6x6=36 7 6 6x7=42 8 6 6x8=48 9 6 6x9=54 7 7 7x7=49 8 7 7x8=56 9 7 7x9=63 8 8 8x8=64 9 8 8x9=72 9 9 9x9=81 (45 行受影响)-------------------------------------------------分割线-------------------------------------------==================================================================================================--1.select * from t4 pivot (max(val) for col in([9],[8],[7],[6],[5],[4],[3],[2],[1]))b------------------------------------------------row 9 8 7 6 5 4 3 2 1----------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------1 NULL NULL NULL NULL NULL NULL NULL NULL 1x1= 1 2 NULL NULL NULL NULL NULL NULL NULL 2x2= 4 1x2= 2 3 NULL NULL NULL NULL NULL NULL 3x3= 9 2x3= 6 1x3= 3 4 NULL NULL NULL NULL NULL 4x4=16 3x4=12 2x4= 8 1x4= 4 5 NULL NULL NULL NULL 5x5=25 4x5=20 3x5=15 2x5=10 1x5= 5 6 NULL NULL NULL 6x6=36 5x6=30 4x6=24 3x6=18 2x6=12 1x6= 6 7 NULL NULL 7x7=49 6x7=42 5x7=35 4x7=28 3x7=21 2x7=14 1x7= 7 8 NULL 8x8=64 7x8=56 6x8=48 5x8=40 4x8=32 3x8=24 2x8=16 1x8= 8 9 9x9=81 8x9=72 7x9=63 6x9=54 5x9=45 4x9=36 3x9=27 2x9=18 1x9= 9 (9 行受影响)-------------------------------------------------------2.select * from t4 pivot (max(val) for col in([1],[2],[3],[4],[5],[6],[7],[8],[9]))b-------------------------------------------------------row 1 2 3 4 5 6 7 8 9----------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------1 1x1= 1 NULL NULL NULL NULL NULL NULL NULL NULL2 1x2= 2 2x2= 4 NULL NULL NULL NULL NULL NULL NULL3 1x3= 3 2x3= 6 3x3= 9 NULL NULL NULL NULL NULL NULL4 1x4= 4 2x4= 8 3x4=12 4x4=16 NULL NULL NULL NULL NULL5 1x5= 5 2x5=10 3x5=15 4x5=20 5x5=25 NULL NULL NULL NULL6 1x6= 6 2x6=12 3x6=18 4x6=24 5x6=30 6x6=36 NULL NULL NULL7 1x7= 7 2x7=14 3x7=21 4x7=28 5x7=35 6x7=42 7x7=49 NULL NULL8 1x8= 8 2x8=16 3x8=24 4x8=32 5x8=40 6x8=48 7x8=56 8x8=64 NULL9 1x9= 9 2x9=18 3x9=27 4x9=36 5x9=45 6x9=54 7x9=63 8x9=72 9x9=81 (9 行受影响)----------------------------------------------------------3.select * from t4 pivot (max(val) for row in([1],[2],[3],[4],[5],[6],[7],[8],[9]))b---------------------------------------------------------col 1 2 3 4 5 6 7 8 9----------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------1 1x1= 1 1x2= 2 1x3= 3 1x4= 4 1x5= 5 1x6= 6 1x7= 7 1x8= 8 1x9= 9 2 NULL 2x2= 4 2x3= 6 2x4= 8 2x5=10 2x6=12 2x7=14 2x8=16 2x9=18 3 NULL NULL 3x3= 9 3x4=12 3x5=15 3x6=18 3x7=21 3x8=24 3x9=27 4 NULL NULL NULL 4x4=16 4x5=20 4x6=24 4x7=28 4x8=32 4x9=36 5 NULL NULL NULL NULL 5x5=25 5x6=30 5x7=35 5x8=40 5x9=45 6 NULL NULL NULL NULL NULL 6x6=36 6x7=42 6x8=48 6x9=54 7 NULL NULL NULL NULL NULL NULL 7x7=49 7x8=56 7x9=63 8 NULL NULL NULL NULL NULL NULL NULL 8x8=64 8x9=72 9 NULL NULL NULL NULL NULL NULL NULL NULL 9x9=81 (9 行受影响)-----------------------------------------------------------4.select * from t4 pivot (max(val) for row in([9],[8],[7],[6],[5],[4],[3],[2],[1]))b---------------------------------------------------------col 9 8 7 6 5 4 3 2 1----------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------1 1x9= 9 1x8= 8 1x7= 7 1x6= 6 1x5= 5 1x4= 4 1x3= 3 1x2= 2 1x1= 1 2 2x9=18 2x8=16 2x7=14 2x6=12 2x5=10 2x4= 8 2x3= 6 2x2= 4 NULL3 3x9=27 3x8=24 3x7=21 3x6=18 3x5=15 3x4=12 3x3= 9 NULL NULL4 4x9=36 4x8=32 4x7=28 4x6=24 4x5=20 4x4=16 NULL NULL NULL5 5x9=45 5x8=40 5x7=35 5x6=30 5x5=25 NULL NULL NULL NULL6 6x9=54 6x8=48 6x7=42 6x6=36 NULL NULL NULL NULL NULL7 7x9=63 7x8=56 7x7=49 NULL NULL NULL NULL NULL NULL8 8x9=72 8x8=64 NULL NULL NULL NULL NULL NULL NULL9 9x9=81 NULL NULL NULL NULL NULL NULL NULL NULL(9 行受影响)----------------------------------------------------------------
[解决办法]
学习了，呵呵。。。
[解决办法]
不错啊
[解决办法]
膜拜
[解决办法]
谢谢分享，膜拜！
[解决办法]
- SQL code
/*标题：普通行列转换(version 2.0)作者：爱新觉罗.毓华(十八年风雨,守得冰山雪莲花开)时间：2008-03-09地点：广东深圳说明：普通行列转换(version 1.0)仅针对sql server 2000提供静态和动态写法，version 2.0增加sql server 2005的有关写法。问题：假设有张学生成绩表(tb)如下:姓名 课程 分数张三 语文 74张三 数学 83张三 物理 93李四 语文 74李四 数学 84李四 物理 94想变成(得到如下结果)： 姓名 语文 数学 物理 ---- ---- ---- ----李四 74 84 94张三 74 83 93-------------------*/create table tb(姓名 varchar(10) , 课程 varchar(10) , 分数 int)insert into tb values('张三' , '语文' , 74)insert into tb values('张三' , '数学' , 83)insert into tb values('张三' , '物理' , 93)insert into tb values('李四' , '语文' , 74)insert into tb values('李四' , '数学' , 84)insert into tb values('李四' , '物理' , 94)go--SQL SERVER 2000 静态SQL,指课程只有语文、数学、物理这三门课程。(以下同)select 姓名 as 姓名 , max(case 课程 when '语文' then 分数 else 0 end) 语文, max(case 课程 when '数学' then 分数 else 0 end) 数学, max(case 课程 when '物理' then 分数 else 0 end) 物理from tbgroup by 姓名--SQL SERVER 2000 动态SQL,指课程不止语文、数学、物理这三门课程。(以下同)declare @sql varchar(8000)set @sql = 'select 姓名 'select @sql = @sql + ' , max(case 课程 when ''' + 课程 + ''' then 分数 else 0 end) [' + 课程 + ']'from (select distinct 课程 from tb) as aset @sql = @sql + ' from tb group by 姓名'exec(@sql) --SQL SERVER 2005 静态SQL。select * from (select * from tb) a pivot (max(分数) for 课程 in (语文,数学,物理)) b--SQL SERVER 2005 动态SQL。declare @sql varchar(8000)select @sql = isnull(@sql + '],[' , '') + 课程 from tb group by 课程set @sql = '[' + @sql + ']'exec ('select * from (select * from tb) a pivot (max(分数) for 课程 in (' + @sql + ')) b')
[解决办法]
看得眼晕了。。
[解决办法]
我也来整个
- SQL code
select '1*'+a+'='+CAST(1*1*(case when a>=1Then a else NULL end) as varchar) as [1],'2*'+a+'='+CAST(2*1*(case when a>=2Then a else NULL end) as varchar) as [2],'3*'+a+'='+CAST(3*1*(case when a>=3Then a else NULL end) as varchar) as [3],'4*'+a+'='+CAST(4*1*(case when a>=4Then a else NULL end) as varchar) as [4],'5*'+a+'='+CAST(5*1*(case when a>=5Then a else NULL end) as varchar) as [5],'6*'+a+'='+CAST(6*1*(case when a>=6Then a else NULL end) as varchar) as [6],'7*'+a+'='+CAST(7*1*(case when a>=7Then a else NULL end) as varchar) as [7],'8*'+a+'='+CAST(8*1*(case when a>=8Then a else NULL end) as varchar) as [8],'9*'+a+'='+CAST(9*1*(case when a>=9Then a else NULL end) as varchar) as [9]from ( select top 9 a from(select top 9 CAST(Row_Number()over (order by name) as varchar(10) ) as a from master..spt_values) aa order by a desc)bselect '1*'+a+'='+CAST(1*1*(case when a>=1Then a else NULL end) as varchar) as [1],'2*'+a+'='+CAST(2*1*(case when a>=2Then a else NULL end) as varchar) as [2],'3*'+a+'='+CAST(3*1*(case when a>=3Then a else NULL end) as varchar) as [3],'4*'+a+'='+CAST(4*1*(case when a>=4Then a else NULL end) as varchar) as [4],'5*'+a+'='+CAST(5*1*(case when a>=5Then a else NULL end) as varchar) as [5],'6*'+a+'='+CAST(6*1*(case when a>=6Then a else NULL end) as varchar) as [6],'7*'+a+'='+CAST(7*1*(case when a>=7Then a else NULL end) as varchar) as [7],'8*'+a+'='+CAST(8*1*(case when a>=8Then a else NULL end) as varchar) as [8],'9*'+a+'='+CAST(9*1*(case when a>=9Then a else NULL end) as varchar) as [9]from ( select top 9 a from(select top 9 CAST(Row_Number()over (order by name) as varchar(10) ) as a from master..spt_values) aa order by a asc)bselect '1*'+a+'='+CAST(1*1*(case when a<=1Then a else NULL end) as varchar) as [1],'2*'+a+'='+CAST(2*1*(case when a<=2Then a else NULL end) as varchar) as [2],'3*'+a+'='+CAST(3*1*(case when a<=3Then a else NULL end) as varchar) as [3],'4*'+a+'='+CAST(4*1*(case when a<=4Then a else NULL end) as varchar) as [4],'5*'+a+'='+CAST(5*1*(case when a<=5Then a else NULL end) as varchar) as [5],'6*'+a+'='+CAST(6*1*(case when a<=6Then a else NULL end) as varchar) as [6],'7*'+a+'='+CAST(7*1*(case when a<=7Then a else NULL end) as varchar) as [7],'8*'+a+'='+CAST(8*1*(case when a<=8Then a else NULL end) as varchar) as [8],'9*'+a+'='+CAST(9*1*(case when a<=9Then a else NULL end) as varchar) as [9]from ( select top 9 a from(select top 9 CAST(Row_Number()over (order by name) as varchar(10) ) as a from master..spt_values) aa order by a asc)bselect '1*'+a+'='+CAST(1*1*(case when a<=1Then a else NULL end) as varchar) as [1],'2*'+a+'='+CAST(2*1*(case when a<=2Then a else NULL end) as varchar) as [2],'3*'+a+'='+CAST(3*1*(case when a<=3Then a else NULL end) as varchar) as [3],'4*'+a+'='+CAST(4*1*(case when a<=4Then a else NULL end) as varchar) as [4],'5*'+a+'='+CAST(5*1*(case when a<=5Then a else NULL end) as varchar) as [5],'6*'+a+'='+CAST(6*1*(case when a<=6Then a else NULL end) as varchar) as [6],'7*'+a+'='+CAST(7*1*(case when a<=7Then a else NULL end) as varchar) as [7],'8*'+a+'='+CAST(8*1*(case when a<=8Then a else NULL end) as varchar) as [8],'9*'+a+'='+CAST(9*1*(case when a<=9Then a else NULL end) as varchar) as [9]from ( select top 9 a from(select top 9 CAST(Row_Number()over (order by name) as varchar(10) ) as a from master..spt_values) aa order by a desc)b
[解决办法]
[解决办法]
恩，行转列顶起
[解决办法]
[解决办法]
好多9*9 的贴
[解决办法]
恩，这个有点意思～～
[解决办法]
怎么又是9*9 没事写写霍夫曼数列 斐波那契数列