共同好友

原创于 2019-06-02 11:10:06 发布 · 306 阅读

0 ·

CC 4.0 BY-SA版权

Datawhale之MySQL 专栏收录该内容

43 篇文章

订阅专栏

用户表，只有uid,uid_friend两列，数共同好友最多的好友，以及共同好友的数量

CREATE TABLE fri (
		uid        INT    NOT NULL,
        uid_friend INT NOT NULL );
INSERT INTO fri VALUES (1,2);
INSERT INTO fri VALUES (1,3);
INSERT INTO fri VALUES (1,4);
INSERT INTO fri VALUES (2,1);
INSERT INTO fri VALUES (3,1);
INSERT INTO fri VALUES (3,4);
INSERT INTO fri VALUES (4,1);
INSERT INTO fri VALUES (4,3);

SELECT f1.uid ,count(*) FROM fri f1,fri f2 WHERE f1.uid_friend=f2.uid_friend and f1.uid <> f2.uid
GROUP BY f1.uid
Order by count(*) DESC
LIMIT 1;