从MySQL获取朋友的名字列表 [英] Getting a list of friend's names from MySQL

问题描述

我有一个名为friends的表；

I have a table named friends;

    friends
id   uid   fid
1     1     2   (1 is a friend of 2 and vice versa)
2     1     3   (1 is a friend of 3 and vice versa)
3     4     1   (1 is a friend of 4 and vice versa)
4     5     1   (1 is a friend of 5 and vice versa)

以及对应的用户表；

  users
uid  name
1    mike
2    john
3    karl
4    mary
5    tony

这似乎不起作用:

SELECT name FROM users LEFT JOIN friends ON friends.uid=users.uid WHERE friends.uid='1' OR friends.fid='1'

要获取麦克的所有朋友的名字，我的查询应该是什么?

What should my query be to get all the names of mike's friends?

推荐答案

这只需一个易于索引的查询即可完成；

This should do it just fine with a single, easy to index, query;

SELECT name FROM users u
JOIN friends f 
  ON u.uid = f.uid OR u.uid = f.fid
WHERE (f.uid=1 OR f.fid=1) 
  AND u.uid<>1;

演示此处.

这篇关于从MySQL获取朋友的名字列表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！