SQL学习笔记：利用表自连接求共同好友

一、背景

如何求微信的共同好友？

二、实操

1.建立测试表

-- 建立测试表
CREATE TABLE wechat_friends 
(
    uid BIGINT, 
    tuid BIGINT
);

-- 插入数据
INSERT INTO wechat_friends(uid, tuid)
VALUES
(100,200),
(100,300),
(100,400),
(200,100),
(200,300),
(200,400),
(300,100),
(300,200),
(400,100);

-- 查询
SELECT * FROM wechat_friends;

2.分析

uid 表示每个用户在微信后台的用户 id。

tuid 表示 uid 对应的微信好友 id。

tuid	好友1	好友2
200	100	300
300	100	200

再对好友1、好友2进行 group by 操作，计算 tuid 个数即可。

3.自连接

-- 自连接
SELECT a.uid AS a_uid,
       a.tuid AS a_tuid,
       b.uid AS b_uid,
       b.tuid AS b_tuid
FROM
(
    SELECT * FROM wechat_friends
) a
INNER JOIN
(
    SELECT * FROM wechat_friends
) b
ON a.tuid = b.tuid -- 21行
AND a.uid < b.uid -- 6行

4.分组统计

SELECT a_uid,
       b_uid,
       COUNT(a_tuid) AS cnt
FROM
(
	SELECT a.uid AS a_uid,
	       a.tuid AS a_tuid,
	       b.uid AS b_uid,
	       b.tuid AS b_tuid
	FROM
	(
	    SELECT * FROM wechat_friends
	) a
	INNER JOIN
	(
	    SELECT * FROM wechat_friends
	) b
	ON a.tuid = b.tuid -- 21行
	AND a.uid < b.uid -- 6行
) dd
GROUP BY a_uid, b_uid
/*
a_uid	b_uid	cnt
100	200	2
100	300	1
200	300	1
200	400	1
300	400	1
*/