상큼하진 않지만

눈에 보이는대로 기술하였습니다;

상큼한 답변은 다음 분이..

  SELECT ID, COUNT (1) cnt
    FROM (SELECT DECODE (a.ID, b.ID, b.f_id, b.ID) ID
            FROM (SELECT DISTINCT DECODE (ID, :ID, f_id, ID) ID
                    FROM tt
                   WHERE (ID = :ID OR f_id = :ID)
                     AND status = '2') a
               , tt b
           WHERE (   a.ID = b.ID
                  OR a.ID = b.f_id)
             AND b.status = '2'
             AND b.ID != :ID
             AND b.f_id != :ID)
   GROUP BY ID
   ORDER BY cnt DESC, ID

저는 이렇게 해봤는데..

상큼하지 않아서...

SELECT AA.ID, COUNT(*) CNT
FROM (
      SELECT ID, F_ID FROM TT WHERE STATUS = 2
      UNION ALL
      SELECT F_ID ID, ID F_ID FROM TT WHERE STATUS = 2
     ) AA
  , (
      SELECT CASE WHEN ID = 'AAA' THEN F_ID
                  WHEN F_ID = 'AAA' THEN ID
             END F_ID
      FROM TT
      WHERE STATUS = 2
        AND (ID = 'AAA' OR F_ID = 'AAA')
    ) BB
WHERE AA.F_ID = BB.F_ID
  AND AA.ID != 'AAA'
GROUP BY ID
ORDER BY CNT DESC, ID
;

SELECT f_id
     , COUNT(*) cnt
  FROM (SELECT id, f_id FROM tt WHERE status = 2
         UNION
        SELECT f_id, id FROM tt WHERE status = 2
        )
 WHERE LEVEL = 2
   AND f_id != CONNECT_BY_ROOT(id)
 START WITH id = 'AAA'
 CONNECT BY NOCYCLE PRIOR f_id = id
 GROUP BY f_id
 ORDER BY cnt DESC, f_id
;