又来一道面试题——用 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

打完收工。