文章目录
向用户推荐好友喜欢的音乐
一、题目
现有三张表样例数据分别为:
1、用户关注表t1_follow记录用户及其关注的人。
2、用户喜欢的音乐t1_music_likes。
3、音乐名字表t1_music。
请给用户1凡尘雨推荐他关注的用户喜欢的音乐名称。
目标结果:
二、分析
本题要给用户1凡尘雨推荐其关注的用户喜欢的音乐名称,主要是考察表之间的关联,并考察行转列及去重相关操作。
1、根据用户关注表和用户喜欢的音乐表进行关联,查询出每个用户喜欢的音乐ID。
2、再关联音乐名字表,关联出对应的音乐名称。
3、行转列并对重复的音乐名称去重,得到最终结果。
维度 | 评分 |
---|---|
题目难度 | ⭐️⭐️⭐️ |
题目清晰度 | ⭐️⭐️⭐️⭐️⭐️ |
业务常见度 | ⭐️⭐️⭐️⭐️⭐️ |
三、SQL实战
1、根据用户关注表和用户喜欢的音乐表进行关联,查询出每个用户关注用户喜欢的音乐ID。
查询语句:
select t1.user_id,
t1.user_name,
t1.follower_id,
t1.follower_name,
t2.music_id
from (select user_id,user_name,follower_name,follower_id
from t1_follow
where user_id = 1) t1
left join
(select user_id,
music_id
from t1_music_likes) t2
on t1.follower_id = t2.user_id;
查询结果:
2、关联音乐名字表,关联出对应的音乐名称。
查询语句:
select t1.user_id,
t1.user_name,
t1.follower_id,
t1.follower_name,
t2.music_id,
t3.music_name
from (select user_id,user_name,follower_name,follower_id
from t1_follow
where user_id = 1) t1
left join
(select user_id,
music_id
from t1_music_likes) t2
on t1.follower_id = t2.user_id
left join
(select music_id,
music_name
from t1_music) t3
on t2.music_id = t3.music_id
;
查询结果:
3、行转列并对重复的音乐名称去重,得到最终结果。
查询语句:
select user_id,
concat_ws(',', collect_list(music_name)) as push_music
from (
select user_id,music_id,max(music_name) as music_name
from (
select t1.user_id,
t1.user_name,
t1.follower_id,
t1.follower_name,
t2.music_id,
t3.music_name
from (select user_id,user_name,follower_name,follower_id
from t1_follow
where user_id = 1
) t1
left join
(select user_id,
music_id
from t1_music_likes
) t2
on t1.follower_id = t2.user_id
left join
(select music_id,
music_name
from t1_music
) t3
on t2.music_id = t3.music_id
) tt
where music_id is not null
group by user_id,music_id
) tt_all
group by user_id;
查询结果:
四、样例数据参考
--建表语句
CREATE TABLE t1_follow (
user_id bigint COMMENT '用户ID',
user_name string COMMENT '用户名',
follower_id bigint COMMENT '关注用户ID',
follower_name string COMMENT '关注用户名'
) COMMENT '用户关注表';
-- 插入数据
insert into t1_follow(user_id,user_name,follower_id,follower_name)
values
(1,'凡尘雨',2,'琴流音'),
(1,'凡尘雨',4,'飘过的浮云'),
(1,'凡尘雨',5,'无法言说的痛')
;
-- 建表语句
CREATE TABLE t1_music_likes (
user_id bigint COMMENT '用户ID',
music_id bigint COMMENT '音乐ID'
) COMMENT '用户喜欢音乐ID';
--插入语句
insert into t1_music_likes(user_id,music_id)
values
(1,10),
(2,20),
(2,30),
(3,20),
(3,30),
(4,40),
(4,50)
;
--建表语句
CREATE TABLE t1_music (
music_id bigint COMMENT '音乐ID',
music_name string COMMENT '音乐名称'
) COMMENT '音乐名字表';
-- 插入语句
insert into t1_music(music_id,music_name)
values
(10,'海阔天空'),
(20,'星辰大海'),
(30,'岁月神偷'),
(40,'爱如潮水'),
(50,'高山流水')
;
- 📢博客主页:https://lansonli.blog.csdn.net
- 📢欢迎点赞 👍 收藏 ⭐留言 📝 如有错误敬请指正!
- 📢本文由 Lansonli 原创,首发于 CSDN博客🙉
- 📢停下休息的时候不要忘了别人还在奔跑,希望大家抓紧时间学习,全力奔赴更美好的生活✨