来，试试用SQL写乖法口决 100分
--SQL写乖法口决--能写出思路与SQL注释，便于大家理解才好，谢谢！ 1 2 3 4 5 6 7 8 9 ----- ------ ------ ------ ------ ------ ------ ------ ------1 1X1=1 2 1X2=2 2X2=4 3 1X3=3 2X3=6 3X3=9 4 1X4=4 2X4=8 3X4=12 4X4=16 5 1X5=5 2X5=10 3X5=15 4X5=20 5X5=25 6 1X6=6 2X6=12 3X6=18 4X6=24 5X6=30 6X6=36 7 1X7=7 2X7=14 3X7=21 4X7=28 5X7=35 6X7=42 7X7=49 8 1X8=8 2X8=16 3X8=24 4X8=32 5X8=40 6X8=48 7X8=56 8X8=64 9 1X9=9 2X9=18 3X9=27 4X9=36 5X9=45 6X9=54 7X9=63 8X9=72 9X9=81
declare @i smallint,@j smallint,@str varchar(100) set @i=1 while @i<=9 begin select @j=1,@str='' while @j<=@i select @str=@str +cast(@j as char(1)) +'*'+cast(@i as char(1)) +'='+cast(@i*@j as char(2)) +space(2) ,@j=@j+1 print @str set @i=@i+1 end
[解决办法]
select a.number , [1]=CASE WHEN 1>A.NUMBER THEN '' ELSE '1'+'X'+ltrim(a.number)+'='+ltrim(1*a.number) END, [2]=CASE WHEN 2>A.NUMBER THEN '' ELSE '2'+'X'+ltrim(a.number)+'='+ltrim(2*a.number) END, [3]=CASE WHEN 3>A.NUMBER THEN '' ELSE '3'+'X'+ltrim(a.number)+'='+ltrim(3*a.number) END, [4]=CASE WHEN 4>A.NUMBER THEN '' ELSE '4'+'X'+ltrim(a.number)+'='+ltrim(4*a.number) END, [5]=CASE WHEN 5>A.NUMBER THEN '' ELSE '5'+'X'+ltrim(a.number)+'='+ltrim(5*a.number) END, [6]=CASE WHEN 6>A.NUMBER THEN '' ELSE '6'+'X'+ltrim(a.number)+'='+ltrim(6*a.number) END, [7]=CASE WHEN 7>A.NUMBER THEN '' ELSE '7'+'X'+ltrim(a.number)+'='+ltrim(7*a.number) END, [8]=CASE WHEN 8>A.NUMBER THEN '' ELSE '8'+'X'+ltrim(a.number)+'='+ltrim(8*a.number) END, [9]=CASE WHEN 9>A.NUMBER THEN '' ELSE '9'+'X'+ltrim(a.number)+'='+ltrim(9*a.number) ENDfrom master..spt_values awhere type='p' and number between 1 and 9 number 1 2 3 4 5 6 7 8 9----------- --------------------------- --------------------------- --------------------------- --------------------------- --------------------------- --------------------------- --------------------------- --------------------------- ---------------------------1 1X1=1 2 1X2=2 2X2=4 3 1X3=3 2X3=6 3X3=9 4 1X4=4 2X4=8 3X4=12 4X4=16 5 1X5=5 2X5=10 3X5=15 4X5=20 5X5=25 6 1X6=6 2X6=12 3X6=18 4X6=24 5X6=30 6X6=36 7 1X7=7 2X7=14 3X7=21 4X7=28 5X7=35 6X7=42 7X7=49 8 1X8=8 2X8=16 3X8=24 4X8=32 5X8=40 6X8=48 7X8=56 8X8=64 9 1X9=9 2X9=18 3X9=27 4X9=36 5X9=45 6X9=54 7X9=63 8X9=72 9X9=81(9 行受影响)
[解决办法]
DECLARE @S VARCHAR(1000)SELECT @S=ISNULL(@S+',','')+'CASE WHEN '+LTRIM(NUMBER)+'>NUMBER THEN '''' ELSE '''+LTRIM(NUMBER)+'X''+LTRIM(NUMBER)+''=''+LTRIM('+ LTRIM(NUMBER)+'*NUMBER) '+' END ['+ LTRIM(NUMBER)+']'from master..spt_values awhere type='p' and number between 1 and 9 SET @S='SELECT NUMBER ,'+@S+ ' FROM master..spt_values awhere type=''p'' and number between 1 and 9 'EXEC(@S)NUMBER 1 2 3 4 5 6 7 8 9----------- --------------------------- --------------------------- --------------------------- --------------------------- --------------------------- --------------------------- --------------------------- --------------------------- ---------------------------1 1X1=1 2 1X2=2 2X2=4 3 1X3=3 2X3=6 3X3=9 4 1X4=4 2X4=8 3X4=12 4X4=16 5 1X5=5 2X5=10 3X5=15 4X5=20 5X5=25 6 1X6=6 2X6=12 3X6=18 4X6=24 5X6=30 6X6=36 7 1X7=7 2X7=14 3X7=21 4X7=28 5X7=35 6X7=42 7X7=49 8 1X8=8 2X8=16 3X8=24 4X8=32 5X8=40 6X8=48 7X8=56 8X8=64 9 1X9=9 2X9=18 3X9=27 4X9=36 5X9=45 6X9=54 7X9=63 8X9=72 9X9=81(9 行受影响)
[解决办法]
with t1 as( select id=number from master..spt_values where type = 'P' and number between 1 and 9 ),t2 as( select a.id,id2=b.id,v=case when a.id>=b.id then ltrim(a.id)+'X'+ltrim(b.id)+'='+ltrim(a.id*b.id) else '' end from t1 a, t1 b)select * from t2 a pivot (max(v) for id2 in ([1],[2],[3],[4],[5],[6],[7],[8],[9])) b/*id 1 2 3 4 5 6 7 8 9----------- -------------------------------------- -------------------------------------- -------------------------------------- -------------------------------------- -------------------------------------- -------------------------------------- -------------------------------------- -------------------------------------- --------------------------------------1 1X1=1 2 2X1=2 2X2=4 3 3X1=3 3X2=6 3X3=9 4 4X1=4 4X2=8 4X3=12 4X4=16 5 5X1=5 5X2=10 5X3=15 5X4=20 5X5=25 6 6X1=6 6X2=12 6X3=18 6X4=24 6X5=30 6X6=36 7 7X1=7 7X2=14 7X3=21 7X4=28 7X5=35 7X6=42 7X7=49 8 8X1=8 8X2=16 8X3=24 8X4=32 8X5=40 8X6=48 8X7=56 8X8=64 9 9X1=9 9X2=18 9X3=27 9X4=36 9X5=45 9X6=54 9X7=63 9X8=72 9X9=81(9 行受影响)*/
[解决办法]
;with c1 as(select number from master..spt_values where type='p' and number>0 and number<10),c2 as(select a.number,convert(varchar,a.number)+'×'+convert(varchar,b.number)+'='+convert(varchar,a.number*b.number)as multifrom c1 a inner join c1 b on b.number<=a.number)select distinct T.* from (select number,stuff((select ' '+multi from c2 where number=a.number for xml path('')),1,1,'') as multi from c2 a)T/*number multi----------- ----------------------------------------------------------------------------------------------------------------1 1×1=12 2×1=2 2×2=43 3×1=3 3×2=6 3×3=94 4×1=4 4×2=8 4×3=12 4×4=165 5×1=5 5×2=10 5×3=15 5×4=20 5×5=256 6×1=6 6×2=12 6×3=18 6×4=24 6×5=30 6×6=367 7×1=7 7×2=14 7×3=21 7×4=28 7×5=35 7×6=42 7×7=498 8×1=8 8×2=16 8×3=24 8×4=32 8×5=40 8×6=48 8×7=56 8×8=649 9×1=9 9×2=18 9×3=27 9×4=36 9×5=45 9×6=54 9×7=63 9×8=72 9×9=81(9 行受影响)*/
[解决办法]
先写一个简单的热热身，可能不太符合题意
declare @i int,@j int,@s varchar(100)select @i=1,@j=1,@s=''while @i<=9begin while @j<=9 and @j<=@i begin set @s=@s+' '+ltrim(@i)+'*'+ltrim(@j) set @j=@j+1 end set @i=@i+1 print @send/** 1*1 1*1 2*2 1*1 2*2 3*3 1*1 2*2 3*3 4*4 1*1 2*2 3*3 4*4 5*5 1*1 2*2 3*3 4*4 5*5 6*6 1*1 2*2 3*3 4*4 5*5 6*6 7*7 1*1 2*2 3*3 4*4 5*5 6*6 7*7 8*8 1*1 2*2 3*3 4*4 5*5 6*6 7*7 8*8 9*9**/
[解决办法]
with t as(select number from master..spt_values a where type = 'P' and number between 1 and 9 )select [1],[2],[3],[4],[5],[6],[7],[8],[9] from (select t1.number w1,t2.number w2, case when t1.number>t2.number then '' elseconvert(char(1),t1.number)+'X'+convert(char(1),t2.number)+'='+convert(char(2),t1.number*t2.number) end w3 from t t1,t t2)apivot (max(w3) for w1 in ([1],[2],[3],[4],[5],[6],[7],[8],[9]))c
[解决办法]
with t as(select number ,convert(char(1),number) digit from master..spt_values a where type = 'P' and number between 1 and 9 )select [1],[2],[3],[4],[5],[6],[7],[8],[9] from (select t1.number w1,t2.number w2, case when t1.number>t2.number then '' elset1.digit+'X'+t2.digit+'='+convert(char(2),t1.number*t2.number) end w3 from t t1,t t2)apivot (max(w3) for w1 in ([1],[2],[3],[4],[5],[6],[7],[8],[9]))c
[解决办法]
乖法口诀来啦!
;with c1 as(select number from master..spt_values where type='p' and number>0 and number<10),c2 as(select a.number,convert(varchar,b.number)+'乖'+convert(varchar,a.number)+'得'+convert(varchar,a.number*b.number)as multifrom c1 a inner join c1 b on b.number<=a.number)select distinct multi from (select number,stuff((select ' '+multi from c2 where number=a.number for xml path('')),1,1,'') as multi from c2 a)T/*multi---------------------------------------------------------------------------------------------------------------- 1乖1得1 1乖2得2 2乖2得4 1乖3得3 2乖3得6 3乖3得9 1乖4得4 2乖4得8 3乖4得12 4乖4得16 1乖5得5 2乖5得10 3乖5得15 4乖5得20 5乖5得25 1乖6得6 2乖6得12 3乖6得18 4乖6得24 5乖6得30 6乖6得36 1乖7得7 2乖7得14 3乖7得21 4乖7得28 5乖7得35 6乖7得42 7乖7得49 1乖8得8 2乖8得16 3乖8得24 4乖8得32 5乖8得40 6乖8得48 7乖8得56 8乖8得64 1乖9得9 2乖9得18 3乖9得27 4乖9得36 5乖9得45 6乖9得54 7乖9得63 8乖9得72 9乖9得81(9 行受影响)*/
[解决办法]
declare @i int,@j int,@result varchar(100)set @i=1while @i<=9begin select @j=1,@result='' while @j<=@i begin set @result=@result+' '+ltrim(@j)+'×'+ltrim(@i) +'='+ltrim(@i*@j)++space(3) set @j=@j+1 end set @i=@i+1 print @resultend……………………………………………………………………………………………………………………补充 1×1=1 1×2=2 2×2=4 1×3=3 2×3=6 3×3=9 1×4=4 2×4=8 3×4=12 4×4=16 1×5=5 2×5=10 3×5=15 4×5=20 5×5=25 1×6=6 2×6=12 3×6=18 4×6=24 5×6=30 6×6=36 1×7=7 2×7=14 3×7=21 4×7=28 5×7=35 6×7=42 7×7=49 1×8=8 2×8=16 3×8=24 4×8=32 5×8=40 6×8=48 7×8=56 8×8=64 1×9=9 2×9=18 3×9=27 4×9=36 5×9=45 6×9=54 7×9=63 8×9=72 9×9=81 …………………………………………………………………………………………………………………………declare @i int,@j int,@result varchar(100)set @i=1while @i<=9begin select @j=1,@result='' while @j<=@i begin set @result=@result+' '+cast(@j as char(1))+'×'+cast(@i as char(1)) +'='+cast(@i*@j as char(2))++space(2) set @j=@j+1 end set @i=@i+1 print @resultend…………………………………………………………………………………………………………………………… 1×1=1 1×2=2 2×2=4 1×3=3 2×3=6 3×3=9 1×4=4 2×4=8 3×4=12 4×4=16 1×5=5 2×5=10 3×5=15 4×5=20 5×5=25 1×6=6 2×6=12 3×6=18 4×6=24 5×6=30 6×6=36 1×7=7 2×7=14 3×7=21 4×7=28 5×7=35 6×7=42 7×7=49 1×8=8 2×8=16 3×8=24 4×8=32 5×8=40 6×8=48 7×8=56 8×8=64 1×9=9 2×9=18 3×9=27 4×9=36 5×9=45 6×9=54 7×9=63 8×9=72 9×9=81
[解决办法]
都差不多，两个循环
declare @i intdeclare @j intdeclare @s varchar(200)set @i=1while @i<=9begin set @j=1 set @s='' while @j<=@i begin set @s=@s + ' '+ltrim(@j)+'*'+ltrim(@i)+'='+ltrim(@j*@i); set @j=@j+1; end print @s set @i=@i+1end