一、背景
如何求微信的共同好友?
二、实操
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
*/
参考链接:一道求微信共同好友数的SQL题