又来一道面试题——用 SQL 来写个小九九

题目要求

使用 sql 写出 8 进制的小七七，如下图所示，

题目分析

什么就小七七，我只听说过小九九，哦，对了，我们之前小学的时候背诵过小九九，那是 10 进制，这样看来的话，我们就知道了，这个题目是让我们使用 8 进制下以内得乘法法则。

先搞小九九

sql 代码如下（hive版本），

with detail as ( select explode( split(repeat(',',8),',')) ),detail_rn as ( select row_number() over() as rn from detail ) select if( rn > 0 , '1*' || rn || '=' || 1*rn , '') as col_1 ,if( rn > 1 , '2*' || rn || '=' || 2*rn , '') as rn_1 ,if( rn > 2 , '3*' || rn || '=' || 3*rn , '') as rn_2 ,if( rn > 3 , '4*' || rn || '=' || 4*rn , '') as rn_3 ,if( rn > 4 , '5*' || rn || '=' || 5*rn , '') as rn_4 ,if( rn > 5 , '6*' || rn || '=' || 6*rn , '') as rn_5 ,if( rn > 6 , '7*' || rn || '=' || 7*rn , '') as rn_6 ,if( rn > 7 , '8*' || rn || '=' || 8*rn , '') as rn_7 ,if( rn > 8 , '9*' || rn || '=' || 9*rn , '') as rn_8 from detail_rn

小七七

第一种解法，

with detail as ( select explode( split(repeat(',',48),',')) ),detail_rn as ( select row_number() over() as rn from detail ),detail_grp_rn as ( select cast((rn+6)/7 as int ) as grp ,row_number() over(partition by cast((rn+6)/7 as int ) order by cast((rn+6)/7 as int )) as grp_rn from detail_rn ),detail_mul as ( select grp ,grp_rn ,if( grp_rn > grp -1 , grp || '*' || grp_rn || '=' || conv(grp*grp_rn,10,8) , '') as col_1 from detail_grp_rn ),detail_collect as ( select grp ,collect_list(col_1) as collect_grp from detail_mul group by grp ),detail_c_collect as ( select collect_list( collect_grp ) as c_collect_grp from detail_collect ) select explode(array(c_collect_grp[0][0] || ' ' || c_collect_grp[1][0] || ' ' || c_collect_grp[2][0] || ' ' || c_collect_grp[3][0] || ' ' || c_collect_grp[4][0] || ' ' || c_collect_grp[5][0] || ' ' || c_collect_grp[6][0] ,c_collect_grp[0][1] || ' ' || c_collect_grp[1][1] || ' ' || c_collect_grp[2][1] || ' ' || c_collect_grp[3][1] || ' ' || c_collect_grp[4][1] || ' ' || c_collect_grp[5][1] || ' ' || c_collect_grp[6][1] ,c_collect_grp[0][2] || ' ' || c_collect_grp[1][2] || ' ' || c_collect_grp[2][2] || ' ' || c_collect_grp[3][2] || ' ' || c_collect_grp[4][2] || ' ' || c_collect_grp[5][2] || ' ' || c_collect_grp[6][2] ,c_collect_grp[0][3] || ' ' || c_collect_grp[1][3] || ' ' || c_collect_grp[2][3] || ' ' || c_collect_grp[3][3] || ' ' || c_collect_grp[4][3] || ' ' || c_collect_grp[5][3] || ' ' || c_collect_grp[6][3] ,c_collect_grp[0][4] || ' ' || c_collect_grp[1][4] || ' ' || c_collect_grp[2][4] || ' ' || c_collect_grp[3][4] || ' ' || c_collect_grp[4][4] || ' ' || c_collect_grp[5][4] || ' ' || c_collect_grp[6][4] ,c_collect_grp[0][5] || ' ' || c_collect_grp[1][5] || ' ' || c_collect_grp[2][5] || ' ' || c_collect_grp[3][5] || ' ' || c_collect_grp[4][5] || ' ' || c_collect_grp[5][5] || ' ' || c_collect_grp[6][5] ,c_collect_grp[0][6] || ' ' || c_collect_grp[1][6] || ' ' || c_collect_grp[2][6] || ' ' || c_collect_grp[3][6] || ' ' || c_collect_grp[4][6] || ' ' || c_collect_grp[5][6] || ' ' || c_collect_grp[6][6])) from detail_c_collect

第二种写法，

with detail as ( select explode( split(repeat(',',6),',')) ),detail_rn as ( select row_number() over() as rn from detail ) select if( rn > 0 , '1*' || rn || '=' || conv(1*rn,10,8) , '') as col_1 ,if( rn > 1 , '2*' || rn || '=' || conv(2*rn,10,8) , '') as rn_1 ,if( rn > 2 , '3*' || rn || '=' || conv(3*rn,10,8) , '') as rn_2 ,if( rn > 3 , '4*' || rn || '=' || conv(4*rn,10,8) , '') as rn_3 ,if( rn > 4 , '5*' || rn || '=' || conv(5*rn,10,8) , '') as rn_4 ,if( rn > 5 , '6*' || rn || '=' || conv(6*rn,10,8) , '') as rn_5 ,if( rn > 6 , '7*' || rn || '=' || conv(7*rn,10,8) , '') as rn_6 from detail_rn

打完收工。