mysql - 查找使用SQL接受的 friend 请求的一部分？

我有两个社交网站的表-一个包含发送的所有好友请求的发件人和收件人的名称，另一个包含接受的所有好友请求的发件人和收件人的名称。我创建了一个虚拟数据库-

CREATE TABLE requested ( sender text(10), receiver text(10) );
INSERT INTO requested (sender, receiver) VALUES ('tom', 'jerry');
INSERT INTO requested (sender, receiver) VALUES ('tom', 'adam');
INSERT INTO requested (sender, receiver) VALUES ('tom', 'alice');
INSERT INTO requested (sender, receiver) VALUES ('anne', 'jack');
INSERT INTO requested (sender, receiver) VALUES ('anne', 'bill');

CREATE TABLE accepted ( sender text(10), receiver text(10) );
INSERT INTO accepted (sender, receiver) VALUES ('tom', 'jerry');
INSERT INTO accepted (sender, receiver) VALUES ('tom', 'alice');
INSERT INTO accepted (sender, receiver) VALUES ('anne', 'jack');

如何显示每个用户接受的好友请求的分数？对于这些桌子，我想看看-

tom | 0.6666
anne | 0.5

最佳答案

SELECT
      sender,
      count(*) / (SELECT count(*) FROM requested
                                  WHERE requested.sender = accepted.sender) as 'ratio'
    FROM accepted group by sender

或者

SELECT
      sender,
      (SELECT count(*) FROM accepted
                       WHERE requested.sender = accepted.sender) / count(*) as 'ratio'
    FROM requested group by sender

前一个将忽略没有至少一个回复的发件人。底部的查询将显示这些。

关于mysql - 查找使用SQL接受的 friend 请求的一部分？，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/44092770/