面试题

id course
1,a
1,b
1,c
1,e
2,a
2,c
2,d
2,f
3,a
3,b
3,c
3,e

create table student_course(
num int,
course string)
row format delimited fields terminated by ‘,’;

select num as 学号,course as 选修课程
from student_course
group by course,num;

+—–+——-+–+
| 学号 | 选修课程 |
+—–+——-+–+
| 1 | a |
| 2 | a |
| 3 | a |
| 1 | b |
| 3 | b |
| 1 | c |
| 2 | c |
| 3 | c |
| 2 | d |
| 1 | e |
| 3 | e |
| 2 | f |
+—–+——-+–+

select t1.num,t1.course,t2.course
from
(
select num as num,collect_set(course) as course
from student_course
group by num
) t1
join
(select collect_set(course) as course
from student_course) t2;

+———+——————–+—————————-+–+
| t1.num | t1.course | t2.course |
+———+——————–+—————————-+–+
| 1 | [“a”,”b”,”c”,”e”] | [“a”,”b”,”c”,”e”,”d”,”f”] |
| 2 | [“a”,”c”,”d”,”f”] | [“a”,”b”,”c”,”e”,”d”,”f”] |
| 3 | [“a”,”b”,”c”,”e”] | [“a”,”b”,”c”,”e”,”d”,”f”] |
+———+——————–+—————————-+–+

select t1.num,t2.course
from
(
select num as num,collect_set(course) as course
from student_course
group by num
) t1
join
(select collect_set(course) as course
from student_course) t2;

+———+—————————-+–+
| t1.num | t2.course |
+———+—————————-+–+
| 1 | [“a”,”b”,”c”,”e”,”d”,”f”] |
| 2 | [“a”,”b”,”c”,”e”,”d”,”f”] |
| 3 | [“a”,”b”,”c”,”e”,”d”,”f”] |
+———+—————————-+–+

select s1.num as num,
case
when array_contains(s1.c1,s1.c2[0]) then 0001
else 0000
end as 课程a,

case
when array_contains(s1.c1,s1.c2[1]) then 0001
else 0000
end as 课程b,

case
when array_contains(s1.c1,s1.c2[2]) then 0001
else 0000
end as 课程c,

case
when array_contains(s1.c1,s1.c2[3]) then 0001
else 0000
end as 课程d,

case
when array_contains(s1.c1,s1.c2[4]) then 0001
else 0000
end as 课程e,

case
when array_contains(s1.c1,s1.c2[5]) then 0001
else 0000
end as 课程f

from
(
select t1.num,t1.course as c1,t2.course as c2
from
(
select num as num,collect_set(course) as course
from student_course
group by num
) t1
join
(select collect_set(course) as course
from student_course) t2
) s1;

+——+——+——+——+——+——+——+–+
| num | 课程a | 课程b | 课程c | 课程d | 课程e | 课程f |
+——+——+——+——+——+——+——+–+
| 1 | 1 | 1 | 1 | 1 | 0 | 0 |
| 2 | 1 | 0 | 1 | 0 | 1 | 1 |
| 3 | 1 | 1 | 1 | 1 | 0 | 0 |
+——+——+——+——+——+——+——+–+