hive 考试记录编码转名称

hive 考试记录编码转名称

create table `answer` (
`exam_name` varchar (150), `question_num` varchar (60), `questtion_name` varchar (150), `answer` varchar (30), `answer_name` varchar (150)
);

insert into `answer` (`exam_name`, `question_num`, `questtion_name`, `answer`, `answer_name`) values('exam-a','0101','你最近 5 年的职业(可多选)','A','教师');
insert into `answer` (`exam_name`, `question_num`, `questtion_name`, `answer`, `answer_name`) values('exam-a','0101','你最近 5 年的职业(可多选)','B','医生');
insert into `answer` (`exam_name`, `question_num`, `questtion_name`, `answer`, `answer_name`) values('exam-a','0101','你最近 5 年的职业(可多选)','C','销售人员');
insert into `answer` (`exam_name`, `question_num`, `questtion_name`, `answer`, `answer_name`) values('exam-a','0101','你最近 5 年的职业(可多选)','D','白领');
insert into `answer` (`exam_name`, `question_num`, `questtion_name`, `answer`, `answer_name`) values('exam-b','0102','你去过哪些地方（可多选）','A','北京');
insert into `answer` (`exam_name`, `question_num`, `questtion_name`, `answer`, `answer_name`) values('exam-b','0102','你去过哪些地方（可多选）','B','上海');
insert into `answer` (`exam_name`, `question_num`, `questtion_name`, `answer`, `answer_name`) values('exam-b','0102','你去过哪些地方（可多选）','C','天津');
insert into `answer` (`exam_name`, `question_num`, `questtion_name`, `answer`, `answer_name`) values('exam-b','0102','你去过哪些地方（可多选）','D','武汉');

create table `exam_record` (
`username` varchar (150),`exam_name` varchar (150), `question_num` varchar (150), `answer_coding` varchar (60)
);
insert into `exam_record` (`username`, `exam_name`, `question_num`, `answer_coding`)
values('张三','exam-a','0101','ACD');
insert into `exam_record` (`username`, `exam_name`, `question_num`, `answer_coding`)
values('李四','exam-b','0102','BCD');
insert into `exam_record` (`username`, `exam_name`, `question_num`, `answer_coding`)
values('王五','exam-a','0101','BD');

参考答案：

select d.username,d.exam_name,d.question_num,d.questtion_name, max(case d.result when 'A' then d.answer_name else 'NULL' end) '答案 A', max(case d.result when 'B' then d.answer_name else 'NULL' end) '答案 B', max(case d.result when 'C' then d.answer_name else 'NULL' end) '答案 C', max(case d.result when 'D' then d.answer_name else 'NULL' end) '答案 D' from
(select e.*,a.questtion_name,a.answer,a.answer_name, (case locate(a.answer,e.answer_coding) when 0 then 'NULL' else a.answer end) as 'result' from exam_record e join answer a on (e.exam_name=a.exam_name and
e.question_num=a.question_num) order by e.username) d group by d.username

欢迎来撩 ： 汇总all