网站怎么建设的,网站后台更改首页代码,网站可以在外地备案吗,wordpress设置文章登录可见目录 1 题目2 建表语句3 题解 题目来源#xff1a;腾讯。 1 题目 有两个表#xff0c;朋友关系表user_friend#xff0c;用户步数表user_steps。朋友关系表包含两个字段#xff0c;用户id#xff0c;用户好友的id#xff1b;用户步数表包含两个字段#xff0c;用户id腾讯。 1 题目 有两个表朋友关系表user_friend用户步数表user_steps。朋友关系表包含两个字段用户id用户好友的id用户步数表包含两个字段用户id用户的步数.用户在好友中的排名
-- user_friend 数据
----------------------
| user_id | friend_id |
----------------------
| 1 | 2 |
| 1 | 3 |
| 2 | 1 |
| 2 | 3 |
| 2 | 4 |
| 2 | 5 |
| 3 | 1 |
| 3 | 4 |
| 3 | 5 |
| 4 | 2 |
| 4 | 3 |
| 4 | 5 |
| 5 | 2 |
| 5 | 3 |
| 5 | 4 |
----------------------
--user_friend数据
----------------------------------------
| user_steps.user_id | user_steps.steps |
----------------------------------------
| 1 | 100 |
| 2 | 95 |
| 3 | 90 |
| 4 | 80 |
| 5 | 10 |
----------------------------------------2 建表语句 CREATE TABLE user_friend
(user_id INT,friend_id INT
) ROW FORMAT DELIMITED FIELDS TERMINATED BY \t;-- 插入数据
INSERT INTO user_friend
VALUES (1, 2),(1, 3),(2, 1),(2, 3),(2, 4),(2, 5),(3, 1),(3, 4),(3, 5),(4, 2),(4, 3),(4, 5),(5, 2),(5, 3),(5, 4);CREATE TABLE user_steps
(user_id INT,steps INT
) ROW FORMAT DELIMITED FIELDS TERMINATED BY \t;INSERT INTO user_steps
VALUES (1, 100),(2, 95),(3, 90),(4, 80),(5, 10);3 题解 1列出好友步数并将自己步数添加到结果中
--好友步数
select t1.user_id, t1.friend_id, t2.steps
from user_friend t1
join user_steps t2
on t1.friend_id t2.user_id
union all
-- 自己步数
select user_id, user_id as friend_id, steps
from user_steps执行结果
------------------------------------------
| _u1.user_id | _u1.friend_id | _u1.steps |
------------------------------------------
| 1 | 2 | 95 |
| 1 | 3 | 90 |
| 2 | 1 | 100 |
| 2 | 3 | 90 |
| 2 | 4 | 80 |
| 2 | 5 | 10 |
| 3 | 1 | 100 |
| 3 | 4 | 80 |
| 3 | 5 | 10 |
| 4 | 2 | 95 |
| 4 | 3 | 90 |
| 4 | 5 | 10 |
| 5 | 2 | 95 |
| 5 | 3 | 90 |
| 5 | 4 | 80 |
| 1 | 1 | 100 |
| 2 | 2 | 95 |
| 3 | 3 | 90 |
| 4 | 4 | 80 |
| 5 | 5 | 10 |
------------------------------------------2按照用户分组给每个用户的“好友”进行排名
select tt1.user_id,tt1.friend_id,tt1.steps,row_number() over (partition by tt1.user_id order by tt1.steps desc) as row_num
from (--好友步数select t1.user_id,t1.friend_id,t2.stepsfrom user_friend t1join user_steps t2on t1.friend_id t2.user_idunion all-- 自己步数select user_id,user_id as friend_id,stepsfrom user_steps) tt1执行结果
----------------------------------------------------
| tt1.user_id | tt1.friend_id | tt1.steps | row_num |
----------------------------------------------------
| 1 | 1 | 100 | 1 |
| 1 | 2 | 95 | 2 |
| 1 | 3 | 90 | 3 |
| 2 | 1 | 100 | 1 |
| 2 | 2 | 95 | 2 |
| 2 | 3 | 90 | 3 |
| 2 | 4 | 80 | 4 |
| 2 | 5 | 10 | 5 |
| 3 | 1 | 100 | 1 |
| 3 | 3 | 90 | 2 |
| 3 | 4 | 80 | 3 |
| 3 | 5 | 10 | 4 |
| 4 | 2 | 95 | 1 |
| 4 | 3 | 90 | 2 |
| 4 | 4 | 80 | 3 |
| 4 | 5 | 10 | 4 |
| 5 | 2 | 95 | 1 |
| 5 | 3 | 90 | 2 |
| 5 | 4 | 80 | 3 |
| 5 | 5 | 10 | 4 |
----------------------------------------------------3求取最终结果
select user_id,row_num
from (select tt1.user_id,tt1.friend_id,tt1.steps,row_number() over (partition by tt1.user_id order by tt1.steps desc) as row_numfrom (--好友步数select t1.user_id,t1.friend_id,t2.stepsfrom user_friend t1join user_steps t2on t1.friend_id t2.user_idunion all-- 自己步数select user_id,user_id as friend_id,stepsfrom user_steps) tt1) tt2
where user_id friend_id执行结果
--------------------
| user_id | row_num |
--------------------
| 1 | 1 |
| 2 | 2 |
| 3 | 2 |
| 4 | 3 |
| 5 | 4 |
--------------------
