基于ID连接所有字段的MySQL查询(如通知)

Question

我正在尝试获得facebook的通知风格。我现在很困惑。如何连接所有在同一篇文章上发表评论的用户。

我的数据库如下：

通知表

 id | post_id | to_user_id  | from_user_id |   type      |      datetime      | type
 1     45      101              107          comment       2016-3-10 11:03:06   ads
 2     45      101              106          comment       2016-3-10 12:05:06   ads
 3     45      101              105          comment       2016-3-10 12:08:05   ads
 4     55      101              106          comment       2016-3-10 12:05:06   ads
 5     55      101              105          comment       2016-3-10 12:08:05   ads

寄存器表

 id    |  name
 101      John
 105      Jack
 106      Anna
 107      Leo

Post_ads表

 id |  title  
 45    Some text
 55    other text

现在，我想将来自post_id的3个表post_id和来自from_user_id的register连接起来，并将所有在同一个post_id中进行评论的用户连接起来，如果有两个以上的用户在同一个帖子上进行评论，那么我希望在两个用户名之后显示用户的数量或数量。

我应该说：

 Leo, Anna and 1 other commented on post Some text.
 Anna, Jack commented on post Other text.

以上Leo、Anna和其他用户名来自寄存器表，而像some text, other text一样的post来自post_ads表

我可以连接所有的表并用三行来显示结果，但是如何像上面这样查询结果。这在mysql查询中是可能的吗。我的查询如下：

 SELECT * FROM notification INNER JOIN `register` ON `register`.id = `notification`.from_user_id INNER JOIN `post_ads` ON `post_ads`.id = `notification`.post_id WHERE `notification`.to_user_id = $userid"

Answer 1

请首先检查查询是否适用于您的情况。如果需要的话我可以解释。

查询：

SELECT 
CONCAT(
      substring_index(group_concat(finalTable.name SEPARATOR ','), ',', 2),
      IF((finalTable.total - 2) > 0,CONCAT(' and ',(finalTable.total - 2),' others'),''),
      CONCAT(' commented on post ',finalTable.title,'')
     ) AS output
FROM
(
  SELECT 
    DISTINCT n.post_id,
    n.from_user_id,
    post_ads.title,
    register.`name`,
    t.total
  FROM notification n
  INNER JOIN 
  ( 
    SELECT 
     post_id,
     COUNT(DISTINCT from_user_id) total
    FROM notification
    GROUP BY post_id ) t
  ON n.post_id = t.post_id
 INNER JOIN register ON register.id = n.from_user_id
 INNER JOIN post_ads ON post_ads.id = n.post_id
) finalTable
GROUP BY finalTable.post_id;

输出：

Leo,Anna and 1 others commented on post Some text
Anna,Jack commented on post Other text

SQL FIDDLE DEMO