mysql - 通过相互计数获得 friend 的随机 friend

Question

我有一个用户表和一个 friend 表。用户有用户详细信息， friend 有用户的 friend 。这是用户表的架构..

create table smsusers(
  id varchar(60),
  password varchar(50) not null,
  fname varchar(30) not null,
  lname varchar(30),
  mailid varchar(50) not null,
  gender varchar(10) not null,
  primary key(id)
);

和 friend 表..

create table friends_list(
  friend_of varchar(60) NOT NULL,
  friends_id varchar(60) NOT NULL,
  friendship_status varchar(30),
  friendship_date timestamp NOT NULL,
  primary key(friend_of,friends_id),
  foreign key(friend_of) references smsusers(id)
  ON DELETE CASCADE ON UPDATE CASCADE);

头像

create table profile_pic(pic_id varchar(200),profile_pic_path varchar(1000),small_pic_path varchar(1000),
adddate varchar(100),userid varchar(60),foreign key(userid) references smsusers(id) ON DELETE CASCADE ON UPDATE CASCADE,
primary key(pic_id));

在这里，我想随机获取 friend 的 5 个具有共同好友数的 friend

我试过这个查询

SELECT DISTINCT ff.friends_id,
   concat(u.fname,' ',u.lname),
   pp.small_pic_path,
   count(*) AS mutual_friend_count
    FROM
    friends_list f
    JOIN friends_list ff
    ON ff.friend_of =f.friends_id
    LEFT JOIN smsusers u
    ON
    u.id=ff.friends_id
    LEFT JOIN profile_pic pp
    ON
    ff.friends_id=pp.userid
    WHERE f.friend_of = 1
    AND ff.friends_id NOT IN (1)
GROUP BY friends_id

这个查询显示 friend 的 friend 和我的 friend ，相互计数也不正确。查询我tried .

Answer 1

MySQL 中的

RAND() 函数返回一个随机浮点值，您还可以像这样以随机顺序检索行:

   SELECT * 
     FROM tbl_name 
 ORDER BY RAND();

因此重写您的查询以显示每个用户的好友及其数量，随机选择:

   SELECT DISTINCT u.id,
          CONCAT(u.fname,' ',u.lname) AS 'User',
          GROUP_CONCAT(CONCAT(f.fname,' ',f.lname) SEPARATOR ', ') AS 'Friends',
          COUNT(*) AS mutual_friend_count
     FROM smsusers u
     JOIN friends_list ff
       ON u.id = ff.friends_id
     JOIN smsusers f
       ON ff.friend_of = f.id
  --WHERE u.id = 1
 GROUP BY friends_id
 ORDER BY RAND()
    LIMIT 5;

如果您使用 WHERE 子句，查询将输出用户 Manish Sahu 的好友信息。

编辑:

实现它的另一种方法是创建一个VIEW:

   CREATE VIEW facebook AS
   SELECT u.id,
          CONCAT(u.fname,' ',u.lname) AS 'User',
          ff.friend_of
     FROM smsusers u
     JOIN friends_list ff
       ON u.id = ff.friends_id
     JOIN smsusers f
       ON ff.friend_of = f.id;

然后，使用我们新生成的 VIEW 重写第一个查询:

   SELECT f.*,
          GROUP_CONCAT(friend_of SEPARATOR ', ') AS 'FriendsIDs',
          COUNT(*) AS mutual_friend_count
     FROM facebook f
 GROUP BY id
 ORDER BY RAND()
    LIMIT 5;

然后，您还可以检索随机生成的用户好友列表:

   SELECT f.*,
          (SELECT `User` 
             FROM `facebook` 
            WHERE `friend_of` IN (1)
         ORDER BY RAND()
            LIMIT 1) AS 'RandomFriends'
     FROM facebook f
    WHERE id = 1;

此外，GROUP_CONCAT 函数在调试此类复杂查询时非常有用。

更多信息:

• MySQL 引用手册:RAND() function
• 计算器溢出:How to use GROUP BY to concatenate strings in MySQL?