原始数据:
①使用 from_unixtime函数将日期转换为数字格式
select from_unixtime(unix_timestamp(s_birth,'yyyy-MM-dd hh:mm:ss'),'yyyyMMddhhmmss') from student_hive;
②使用concat函数拼接
select concat(s_id,(from_unixtime(unix_timestamp(s_birth,'yyyy-MM-dd hh:mm:ss'),'yyyyMMddhhmmss'))) from student_hive;
③为数字前补0
select lpad(s_id,3,0) from student_hive;
④ 拼接
select concat(lpad(s_id,3,0),(from_unixtime(unix_timestamp(s_birth,'yyyy-MM-dd hh:mm:ss'),'yyyyMMddhhmmss'))) from student_hive;
⑤整合
select concat(lpad(s_id,3,0),(from_unixtime(unix_timestamp(s_birth,'yyyy-MM-dd hh:mm:ss'),'yyyyMMddhhmmss'))),s_name,s_birth,s_sex from student_hive;
⑥插入新表
select concat(lpad(s_id,3,0),(from_unixtime(unix_timestamp(s_birth,'yyyy-MM-dd hh:mm:ss'),'yyyyMMddhhmmss'))),s_name,s_birth,s_sex from student_hive;
成功!!!