SQL案例学习-微信好友关系

在微信中，两个用户可以相互加为好友。

用两个数据表来存储相关数据，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);