题目要求
使用 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
打完收工。