HQL面试题练习 —— 向用户推荐好友喜欢的音乐

题目来源：腾讯。

1 题目

现有三张表分别为：

用户关注表 t_follow(user_id,follower_id)记录用户ID及其关注的人ID，请给用户1 推荐他关注的用户喜欢的音乐名称

+----------+--------------+
| user_id  | follower_id  |
+----------+--------------+
| 1        | 2            |
| 1        | 4            |
| 1        | 5            |
+----------+--------------+

用户喜欢的音乐t_music_likes(user_id,music_id)

+----------+-----------+
| user_id  | music_id  |
+----------+-----------+
| 1        | 10        |
| 2        | 20        |
| 2        | 30        |
| 3        | 20        |
| 3        | 30        |
| 4        | 40        |
| 4        | 50        |
+----------+-----------+

音乐名字表t_music(music_id,music_name)

+-----------+-------------+
| music_id  | music_name  |
+-----------+-------------+
| 10        | a           |
| 20        | b           |
| 30        | c           |
| 40        | d           |
| 50        | e           |
+-----------+-------------+

2 建表语句

--建表语句
CREATE TABLE t_follow (
user_id bigint COMMENT '用户ID',
follower_id bigint COMMENT '关注用户ID'
) COMMENT '用户关注表'
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
;
-- 插入数据
insert into t_follow(user_id,follower_id)
values
(1,2),
(1,4),
(1,5)
;
-- 建表语句
CREATE TABLE t_music_likes (
user_id bigint COMMENT '用户ID',
music_id bigint COMMENT '音乐ID'
) COMMENT '用户喜欢音乐ID'
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
;
--插入语句
insert into t_music_likes(user_id,music_id)
values
(1,10),
(2,20),
(2,30),
(3,20),
(3,30),
(4,40),
(4,50)
;
--建表语句
CREATE TABLE t_music (
music_id bigint COMMENT '音乐ID',
music_name string COMMENT '音乐名称'
) COMMENT '音乐名字表'
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
;
-- 插入语句
insert into t_music(music_id,music_name)
values
(10,'a'),
(20,'b'),
(30,'c'),
(40,'d'),
(50,'e')
;

3 题解

步骤一：根据用户关注表和用户喜欢的音乐表进行关联，查询出每个用户关注用户喜欢的音乐ID，再筛选出用户1关注用户喜欢的音乐ID；

select
    t1.user_id,t1.follower_id,music_id
from
    t_follow t1
inner join
    t_music_likes t2
on t1.follower_id=t2.user_id
where t1.user_id=1;

执行结果

步骤二：关联音乐名字表，关联出对应的音乐名称；

select
    t1.user_id,t1.follower_id,t2.music_id,music_name
from
    t_follow t1
inner join
    t_music_likes t2
on t1.follower_id=t2.user_id
inner join
    t_music t3
on t2.music_id=t3.music_id
where t1.user_id=1;

执行结果

步骤三：行转列并对重复的音乐名称去重，得到最终结果。

select
    t.user_id,
    concat_ws(",",collect_set(music_name)) push_music
from
    (select
        t1.user_id,t1.follower_id,t2.music_id,music_name
    from
        t_follow t1
    inner join
        t_music_likes t2
    on t1.follower_id=t2.user_id
    inner join
        t_music t3
    on t2.music_id=t3.music_id
    where t1.user_id=1)t
group by t.user_id;

执行结果