Find the user with the most contact

Users send messages to each others message boxes, how to find the user who knows the most other users (receive AND send??). I have SQL Server.

Table:

• USER_ID
• USER_NAME
• USER_GROUP
• MESSAGE_ID
• MESSAGE_MESSAGE
• MESSAGE_FROM_USER
• M_ESSAGE_TO_USER

A us开发者_如何学Cer can send and receive, so who knows the most other users (by send AND receive??)

SQL Server 2005+, Using CTE:

 WITH usr_list AS (
   SELECT x.user_id,
          x.message_from_user AS other_user
     FROM TABLE x
   UNION
   SELECT y.user_id,
          y.message_to_user AS other_user
     FROM TABLE y)
  SELECT TOP 1
         ul.user_id,
         COUNT(*) AS num_friends
    FROM usr_list ul
GROUP BY ul.user_id
ORDER BY num_friends DESC

Non CTE equivalent:

  SELECT TOP 1
         ul.user_id,
         COUNT(*) AS num_friends
    FROM (SELECT x.user_id,
                 x.message_from_user AS other_user
            FROM TABLE x
          UNION
          SELECT y.user_id,
                 y.message_to_user AS other_user
            FROM TABLE y) ul
GROUP BY ul.user_id
ORDER BY num_friends DESC

Untested, but this should ensure that each other user is only counted once, regardless if they were the sender or the recipient, or how many messages they sent or received:

SELECT TOP 1
  user_id,
  friend_count = COUNT(DISTINCT u)
FROM
(
  SELECT user_id,
    u = message_from_user
    FROM table
  UNION ALL
  SELECT user_id,
    u = message_to_user
    FROM table
) AS x
ORDER BY friend_count DESC;