Question

This question already has an answer here:

Possible Duplicate:
MYSQL select mutual friends

我有一个友谊的桌子,友谊只存放在一线。因此没有重复条目。

id  Person1    Person2  status
1         1          2  friend
2         1          3  friend
3         2          3  friend
4         3          4  friend

我要问的是,我的SQL询问(参加、加入)将帮助我找到第1号与第3号个人之间的共同(相互)朋友? 这方面的投入是{1,3},产出应为{2},因为第2号是第1号和第3号的朋友。

Answer 1

如今唯一可能工作的问题是Simon s......,但这种真正超技能――这种复杂的新生问题(2个有2个工会的子座!) 简便地说,你需要做一个 b? 如果像1 000+用户一样,问话会很缓慢,因为耳机、 qua子和子宫的工会,那么几乎不会使用任何指数!

我建议再次思考这一设计,并允许两条两条重复的友谊:

id  Person1    Person2  status
1         1          2  friend
2         2          1  friend
3         1          3  friend
4         3          1  friend

您可能认为,效率不高,但在简化之后,将允许将问题变成简单合并:

select f1.Person2 as common_friend
from friends as f1 join friends as f2
    using (Person2)
where f1.Person1 =  $id1  and f2.Person1 =  $id2  
    and f1.status =  friend  and f2.status =  friend

which! https://stackoverflow.com/a/9982157/684229。我建议,在其它非常新生的数据结构中采用类似的简化程序(重写子库,以便加入),并加快了从永久到混合式的问答程序。

因此,看上去大的间接费用(两行友好)实际上是一种优化:-

此外,还将使像“所有X朋友”这样的询问更加容易。不需要再花钱:

Answer 2

这种质疑的假设是,如果这些条件不能满足于使其发挥作用所需要的微薄条件,那么友谊桌上就没有自我友好,也没有重复。

SELECT fid FROM 
(
    --FIRST PERSON (X) FRIENDLIST
    SELECT 
        (CASE WHEN Person1 = X THEN Person2 ELSE Person1 END) AS fid
    FROM Friendships WHERE (Person1 = X OR Person2 = X) AND status = "friend"
    UNION ALL --DO NOT REMOVE DUPLICATES WITH ALL JOIN
    --SECOND PERSON (Y) FRIENDLIST
    SELECT 
        (CASE WHEN Person1 = Y THEN Person2 ELSE Person1 END) AS fid
    FROM Friendships WHERE (Person1 = Y OR Person2 = Y) AND status = "friend"
) FLIST
GROUP BY fid
HAVING COUNT(*) = 2

Answer 3

还有一个答案。

select 
    (case when f1.person1 = 1 then f1.person2 else f1.person1 end) as fid
from friends f1
where f1.person1 = 1 or f1.person2 = 1
and f1.status =  friend 

intersect

select 
    (case when f1.person1 = 3 then f1.person2 else f1.person1 end) as fid
from friends f1
where f1.person1 = 3 or f1.person2 = 3
and f1.status =  friend

Answer 4

set search_path= tmp ;

DROP TABLE friendship CASCADE;
CREATE TABLE friendship
        ( id integer not null PRIMARY KEY
        , person1 integer not null
        , person2 integer not null
        , status varchar
        , CONSTRAINT pk1 UNIQUE (status,person1,person2)
        , CONSTRAINT pk2 UNIQUE (status,person2,person1)
        , CONSTRAINT neq CHECK (person1 <> person2)
        );

INSERT INTO friendship(id,person1,person2,status) VALUES
 (1,1,2, friend  ) ,(2,1,3, friend  ) ,(3,2,3, friend  ) ,(4,3,4, friend  )
        ;

        -- -----------------------------------------
        -- For implementations that don t have CTEs, 
        -- a view can be used to emulate a CTE.
        -- -----------------------------------------
CREATE VIEW flip AS (
        SELECT person1 AS one
                , person2 AS two
        FROM friendship WHERE status =  friend 
        UNION
        SELECT person2 AS one
                , person1 AS two
        FROM friendship WHERE status =  friend 
        );

SELECT DISTINCT
        f1.two AS common
FROM flip f1
JOIN flip f2 ON f1.two = f2.two
WHERE f1.one = 1
AND f2.one = 3
        ;

DROP VIEW flip;

        -- ------------------------------
        -- The same query with a real CTE
        -- ------------------------------
with flip AS (
        SELECT person1 AS one
                , person2 AS two
        FROM friendship WHERE status =  friend 
        UNION
        SELECT person2 AS one
                , person1 AS two
        FROM friendship WHERE status =  friend 
        )
SELECT DISTINCT
        f1.two AS common
FROM flip f1
JOIN flip f2 ON f1.two = f2.two
WHERE f1.one = 1
AND f2.one = 3
        ;

协商:

SET
DROP TABLE
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "friendship_pkey" for table "friendship"
NOTICE:  CREATE TABLE / UNIQUE will create implicit index "pk1" for table "friendship"
NOTICE:  CREATE TABLE / UNIQUE will create implicit index "pk2" for table "friendship"
CREATE TABLE
INSERT 0 4
CREATE VIEW
 common 
--------
      2
(1 row)

DROP VIEW
 common 
--------
      2
(1 row)

Answer 5

我认为,这只是通过这样做来实现的。

SELECT * FROM friends

WHERE
     (Person1 =  1  or Person2 =  1 ) && 
     (Person1 =  2  or Person2 =  2 ) &&
     status =  friend

鉴于你正试图在1至2人之间找到相互的人选。

Answer 6

我询问,低级用户是否总是Person1,但我却在撰写一份问询,如果真的如此,那就不予以注意。

set @firstParty = 1, @secondParty = 3

select friends_of_first.friend
from (
    select Person2 as friend from friends where Person1 = @firstParty
    union 
    select Person1 as friend from friends where Person2 = @firstParty
    ) as friends_of_first
join (
    select Person2 as friend from friends where Person1 = @secondParty
    union 
    select Person1 as friend from friends where Person2 = @secondParty
    ) as friends_of_second
on friends_of_first.friend = friends_of_second.friend

找到用户朋友的分站可替换成一个@Nirmal-thInk beYond:

select case when f1.person1 = @firstParty then  f1.person2 else f1.person1 end 
from friend f1 where f1.person1 = @firstParty or f1.person2 = @firstParty

我很想知道哪一种替代办法能更好地发挥作用。

Answer 7

如果各种答复或评论之一已经提出,我会抱歉:

select Person2 mutual_friend from 
  (select Person1, Person2 from friends 
      where Person1 in (1,3) union 
   select Person2, Person1 from friends 
      where Person2 in (1,3)
  ) t 
  group by Person2 having count(*) > 1;

Answer 8

内部查询只发给第一人的FRIEND ID,并将其标准化为“FriendID”一栏。如果记录中发现有一人ID = 1人处于第一个岗位,那么它会 gr第二个职位......如果人ID = 1人处于第二个职位,那么它就 gr了第一个职位。

With that being done, we know who the single list of friends are of person 1... Done. Now, join back to the friendship table again, but only for those that FIRST are qualified as one of the friends from person 1... Once that is qualified, then make sure that the other person on the second table is the person 3 that you are looking for the commonality of.

• 确保个人指数1 和他人指数2 ,以利用OR条件。

select
      JustPerson1Friends.FriendID
   from
      ( select
              if( f.Person1 = 1, f.Person2, f.Person1 ) as FriendID
           from
              Friendships f
           where
                   (    f.Person1 = 1
                     OR f.Person2 = 1 )
               AND f.status = "friend" ) JustPerson1Friends
      JOIN Friendships f2
         on  (   JustPerson1Friends.FriendID = f2.Person1
              OR JustPerson1Friends.FriendID = f2.Person2 )
         AND f2.status = "friend"
         AND ( f2.Person1 = 3 OR f2.person2 = 3 )

另一种选择是,将“3”作为共同选择列入结果,因此我们不需要在3年后明确限定。此外,通过使用MySQL Variables,易于描述和执行作为参数。在内部询问之后,DOUBLE在友谊中留下来,以明确检验在X/Y或Y/X组合中找到一个人的包裹。因此,最后条款只是说什么。只要在ESTHER LEFT-JOIN的条件下找到记录,就有一名共同的朋友,并列入结果。

select
      JustPerson1Friends.FriendID
   from
      ( select
              @WantPerson2 as FindInCommonWith,
              if( f.Person1 = @WantPerson1, f.Person2, f.Person1 ) as FriendID
           from
              ( select @WantPerson1 := 1,
                       @WantPerson2 := 3 ) sqlvars
              Friendships f,
              (
           where
                   (    f.Person1 = @WantPerson1
                     OR f.Person2 = @WantPerson2 )
               AND f.status = "friend" ) JustPerson1Friends

      LEFT JOIN Friendships f2
         on JustPerson1Friends.FindInCommonWith = f2.Person1
         AND JustPerson1Friends.FriendID = f2.Person2
         AND f2.status = "friend"

      LEFT JOIN Friendships f3
         on JustPerson1Friends.FindInCommonWith = f2.Person2
         AND JustPerson1Friends.FriendID = f2.Person1
         AND f2.status = "friend"
   where
         f2.Person1 > 0
      OR f3.Person1 > 0

Answer 9

This query returns 22 as the result as it is found common for both 1 and 3 You may have to filter out the distinct PERSON1/PERSON2 If I can optimize this query, I will update it


SELECT DISTINCT (REPLACE(TRANSLATE((WM_CONCAT(DISTINCT F.PERSON1) ||  ,  ||
                                           WM_CONCAT(DISTINCT F.PERSON2)),
                                            1,3 ,
                                              ),
                                    ,
                                   )) AS COMMON_FRIEND
          FROM FRIENDSHIP F
         WHERE UPPER(F.STATUS) =  FRIEND 
         AND ((SELECT DISTINCT WM_CONCAT(F1.PERSON1)
                   FROM FRIENDSHIP F1
                  WHERE F1.PERSON2 =  3 ) LIKE ( %  || F.PERSON1 ||  % ) OR
               (SELECT DISTINCT WM_CONCAT(F1.PERSON2)
                   FROM FRIENDSHIP F1
                  WHERE F1.PERSON1 =  3 ) LIKE ( %  || F.PERSON2 ||  % ))
           AND ((SELECT DISTINCT WM_CONCAT(F1.PERSON1)
                   FROM FRIENDSHIP F1
                  WHERE F1.PERSON2 =  1 ) LIKE ( %  || F.PERSON1 ||  % ) OR
               (SELECT DISTINCT WM_CONCAT(F1.PERSON2)
                   FROM FRIENDSHIP F1
                  WHERE F1.PERSON1 =  1 ) LIKE ( %  || F.PERSON2 ||  % ))
           AND NOT ((F.PERSON1 =  1  AND F.PERSON2 =  3 ) OR
                (F.PERSON1 =  3  AND F.PERSON2 =  1 ))

Answer 10

尽管我建议不要照此办理。在这种情况下,我总是选择保存两本关系副本,每一份。

SELECT IF(f1.person1 IN ($id1, $id3), f1.person2, f1.person1) AS mutual_friend
FROM friends f1
INNER JOIN friends f2
    ON (f1.person1 = $id1 AND f2.person1 = $id3 AND f1.person2 = f2.person2)
    OR (f1.person1 = $id1 AND f2.person2 = $id3 AND f1.person2 = f2.person1)
    OR (f1.person2 = $id1 AND f2.person1 = $id3 AND f1.person1 = f2.person2)
    OR (f1.person2 = $id1 AND f2.person2 = $id3 AND f1.person1 = f2.person1)
WHERE f1.status =  friend  AND f2.status =  friend

Answer 11

id  Person1    Person2  status
1         1          2  friend
2         1          3  friend
3         2          3  friend
4         3          4  friend


  SELECT
    DISTINCT
    F1.Person
  FROM
    --Friends of 1
    (
    SELECT F.Person1 Person FROM People F WHERE F.Person2 = 1 AND F.status =  friend 
    UNION
    SELECT F.Person2 Person FROM People F WHERE F.Person1 = 1 AND F.status =  friend 
    ) F1
    INNER JOIN
    (
    --Friends of 3
    SELECT F.Person1 Person FROM People F WHERE F.Person2 = 3 AND F.status =  friend 
    UNION
    SELECT F.Person2 Person FROM People F WHERE F.Person1 = 3 AND F.status =  friend 
    ) F2 ON
      F2.Person = F1.Person

产出:

Person
2

友情链接