Friends (user pairs by mutual answered questions)

1

Please login or register to vote for this query.

(click on this box to dismiss)

スタック・オーバーフロー

プログラマーとプログラミングに熱心な人々のための質問と回答

WITH Raw AS (
  SELECT Q.OwnerUserId AS QUserId, A.OwnerUserId AS AUserId, COUNT(*) AS Count
  FROM Posts Q, Posts A
  WHERE
    A.ParentId = Q.Id
    AND
    A.OwnerUserId IS NOT NULL
    AND
    Q.OwnerUserId IS NOT NULL
    AND
    A.OwnerUserId <> Q.OwnerUserId
  GROUP BY Q.OwnerUserId, A.OwnerUserId
)
SELECT
  A.QUserId AS [User Link],
  B.QUserId AS [User Link],
  A.Count AS Count1,
  B.Count AS Count2,
  CASE WHEN A.Count > B.Count THEN B.Count ELSE A.Count END AS Count
FROM Raw A, Raw B
WHERE
  A.QUserId = B.AUserId
  AND
  A.AUserId = B.QUserId
  AND
  (
    A.Count > B.Count
    OR
    (
      A.Count = B.Count
      AND
      A.QUserId < B.QUserId
    )
  )
ORDER BY Count DESC, A.QUserId, B.QUserId

Enter Parameters

Options:
Switch to meta site
loading Hold tight while we fetch your results