在微信中,两个用户可以相互加为好友。
用两个数据表来存储相关数据,t_user表和t_friend表。
这里需要注意的是两个用户成为好友后,在t_friend表中会有两条记录,以user_id为1和2的两个用户为例,可以看到有两条记录。
上图表示t_friend表中存储的用户好友关系,用直线连接的用户表示彼此间为好友关系。
1.查看好友列表
查询姓名为“刘一”用户的好友列表信息
实现SQL:
SELECT
f.user_id AS "用户ID",
u.user_name AS "好友姓名",
f.friend_id AS "好友ID"
FROM t_friend f
JOIN t_user u ON u.user_id=f.friend_id
WHERE f.user_id=(SELECT user_id FROM t_user WHERE user_name = '刘一');
执行结果:
2.查看共同好友
查询姓名为“张三”和“李四”两个用户的共同好友
实现SQL:
WITH f1(user_name,friend_id) AS (
SELECT
u.user_name AS "好友姓名",
f.friend_id AS "好友ID"
FROM t_friend f
JOIN t_user u ON u.user_id=f.friend_id
WHERE f.user_id=(SELECT user_id FROM t_user WHERE user_name = '张三')
),
f2(user_name,friend_id) AS (
SELECT
u.user_name AS "好友姓名",
f.friend_id AS "好友ID"
FROM t_friend f
JOIN t_user u ON u.user_id=f.friend_id
WHERE f.user_id=(SELECT user_id FROM t_user WHERE user_name = '李四')
)
SELECT user_name,friend_id FROM f1
INTERSECT
SELECT user_name,friend_id FROM f2;
执行结果:
SQL分析:
1.使用WITH查询,将“张三”和“李四”两个用户的好友分别存放在f1和f2两个变量中,这两个变量的值是两张表。
2.通过使用交集运算的运算符INTERSECT,获取两个查询结果集中的共同部分,最后得到两个用户的共同好友。
3.可能认识的人
社交软件通常实现了推荐好友的功能:一方面,其可能读取了该用户的手机通讯录,并找到已经在系统中注册,但不属于该用户好友的用户进行推荐;另一方面,其会找出和该用户不是好友,但是有共同好友的用户进行推荐。
查询出可以推荐给“陈二”的用户,包含两个逻辑:
1.该用户不是“陈二”的好友。
2.该用户是“陈二”的间接好友,也就是好友的好友。
实现SQL:
--陈二好友
WITH f1(user_name,friend_id) AS (
SELECT
u.user_name AS "好友姓名",
f.friend_id AS "好友ID"
FROM t_friend f
JOIN t_user u ON u.user_id=f.friend_id
WHERE f.user_id=(SELECT user_id FROM t_user WHERE user_name = '陈二')
),
--陈二好友的好友
f2(user_name,friend_id) AS (
SELECT
u.user_name AS "好友姓名",
f.friend_id AS "好友ID"
FROM t_friend f
JOIN t_user u ON u.user_id=f.friend_id
JOIN f1 ON f1.friend_id=f.user_id
)
SELECT user_name AS "可能认识的好友",friend_id AS "可能认识的好友ID",
count(1) AS "共同好友个数"
FROM f2
WHERE f2.user_name != '陈二' --过滤陈二自己
AND f2.user_name NOT IN (SELECT user_name FROM f1) --过滤陈二自己的好友
GROUP BY user_name,friend_id
执行结果:
SQL分析:
1.使用WITH查询,将“陈二”的好友和“陈二”好友的好友分别存放在f1和f2两个变量中,这两个变量的值是两张表。
2.通过查询f2表,并从中排除"陈二"及"陈二"自己的好友记录,最后得到“陈二”可能认识的人。
4.用户关系链
以“赵六”和“孙七”为例,查找他们之间的好友关系链:
实现SQL:
WITH relation(userid,fid,hops,path) AS (
SELECT tf.user_id,tf.friend_id,0 hops,
',' || tf.user_id || ',' || tf.friend_id AS path
FROM t_friend tf
WHERE tf.user_id=(SELECT user_id FROM t_user WHERE user_name = '赵六')
UNION ALL
SELECT r.userid,f.friend_id,hops+1,path||','||f.friend_id
FROM t_friend f
JOIN relation r ON r.fid=f.user_id
WHERE instr(r.path,','||f.friend_id||',')=0
AND r.hops <= 6
)
SELECT userid,fid,hops,substr(path,2)
FROM relation
WHERE fid = (SELECT user_id FROM t_user WHERE user_name = '孙七')
执行结果:
SQL分析:
1.先查询出“赵六”的好友,语句如下图所示:
userid为“赵六”的ID,fid为“赵六”好友的ID,hops为两个好友之间间隔的人数,path为查找的路径信息。
2.利用WITH语句实现递归查询,直到hops的值大于6时结束递归。
3.从变量relation中查询出好友为“孙七”的记录。
上面的查询中用到了WITH语句的递归用法,具体使用方法参见下面文章:
https://blog.csdn.net/liangmengbk/article/details/124235489
示例表和脚本:
-- 创建用户表t_user
CREATE TABLE t_user(
user_id INTEGER PRIMARY KEY,
user_name VARCHAR(50) NOT NULL
);
INSERT INTO t_user VALUES(1, '刘一');
INSERT INTO t_user VALUES(2, '陈二');
INSERT INTO t_user VALUES(3, '张三');
INSERT INTO t_user VALUES(4, '李四');
INSERT INTO t_user VALUES(5, '王五');
INSERT INTO t_user VALUES(6, '赵六');
INSERT INTO t_user VALUES(7, '孙七');
INSERT INTO t_user VALUES(8, '周八');
INSERT INTO t_user VALUES(9, '吴九');
-- 创建好友关系表t_friend
CREATE TABLE t_friend(
user_id INTEGER NOT NULL,
friend_id INTEGER NOT NULL,
PRIMARY KEY (user_id, friend_id)
);
INSERT INTO t_friend VALUES(1, 2);
INSERT INTO t_friend VALUES(2, 1);
INSERT INTO t_friend VALUES(1, 3);
INSERT INTO t_friend VALUES(3, 1);
INSERT INTO t_friend VALUES(1, 4);
INSERT INTO t_friend VALUES(4, 1);
INSERT INTO t_friend VALUES(1, 7);
INSERT INTO t_friend VALUES(7, 1);
INSERT INTO t_friend VALUES(1, 8);
INSERT INTO t_friend VALUES(8, 1);
INSERT INTO t_friend VALUES(2, 3);
INSERT INTO t_friend VALUES(3, 2);
INSERT INTO t_friend VALUES(2, 5);
INSERT INTO t_friend VALUES(5, 2);
INSERT INTO t_friend VALUES(3, 4);
INSERT INTO t_friend VALUES(4, 3);
INSERT INTO t_friend VALUES(4, 6);
INSERT INTO t_friend VALUES(6, 4);
INSERT INTO t_friend VALUES(5, 8);
INSERT INTO t_friend VALUES(8, 5);
INSERT INTO t_friend VALUES(7, 8);
INSERT INTO t_friend VALUES(8, 7);