SQL 两个一对多关系 - 计数记录

Question

我有一个feed表，feedComment表和feedLike表。

我需要在一个查询中从数据库中选择所有提要，其中包含评论和喜欢的数量。

这是我到目前为止所拥有的：

SELECT
            `feed`.`id` AS `feedID`,
            `feed`.`companyID`,
            `feed`.`eventID`,
            `feed`.`memberID` AS `personID`,
            `feed`.`title`,
            `feed`.`text`,
            `feed`.`shares`,
            UNIX_TIMESTAMP(`feed`.`date`) AS `date`,
            COUNT(`feedComment`.`id`) AS `comments`
FROM `feed`
LEFT JOIN `feedComment`
ON `feedComment`.`feedID` = `feed`.`id`
GROUP BY `feed`.`id`

这适用于feedComment和feedLike分开。如果我尝试同时使用它们，它不起作用。

我该如何做到这一点？

Answer 1

鉴于您目前所拥有的，您可以添加一个内联视图以从 feedLike 返回计数。

 SELECT `feed`.`id` AS `feedID`,
        `feed`.`companyID`,
        `feed`.`eventID`,
        `feed`.`memberID` AS `personID`,
        `feed`.`title`,
        `feed`.`text`,
        `feed`.`shares`,
        UNIX_TIMESTAMP(`feed`.`date`) AS `date`,
        IFNULL(`countLike`.`cnt`,0) AS `likes`,
        COUNT(`feedComment`.`id`) AS `comments`
   FROM `feed`
   LEFT
   JOIN `feedComment`
     ON `feedComment`.`feedID` = `feed`.`id`
   -- outer join to inline view (MySQL derived table)
   LEFT
   JOIN (
          SELECT `feedLike`.`feedID`
               , COUNT(`feedLike`.`id`) AS `cnt`
            FROM `feedLike`
           GROUP BY `feedLike`.`feedID`
        ) `countLike`
     ON `countLike`.`feedID` = `feed`.`id`
  GROUP BY `feed`.`id`

括号之间的查询将被执行，结果将是MySQL所说的"派生表"。我们为该派生表分配一个名称... countLike .对于外部查询，它就像countLike是一个实际的表。

IFNULL() 函数在外部选择中使用，以将可能的 NULL 值替换为零。我们这样做不是强制性的，但通常人们喜欢将"零"作为计数返回，而不是 NULL。

请注意，派生表可能存在严重的性能问题。（最新版本的MySQL解决了其中一些问题，例如派生表上缺少索引。

同样的方法也可以应用于从feedComment表中获取计数。特别是如果没有其他需要按feed.id分组.

 SELECT feed.id                    AS `feedID`
      , feed.companyID
      , feed.eventID
      , feed.memberID              AS `personID`
      , feed.title
      , feed.text
      , feed.shares
      , UNIX_TIMESTAMP(feed.date)  AS `date`
      , IFNULL(countLike.cnt,0)    AS `likes`
      , IFNULL(countComment.cnt,0) AS `comments`
   FROM feed
   LEFT
   JOIN ( SELECT c.feedID
               , COUNT(1) AS cnt
            FROM feedComment c
           GROUP BY c.feedID
        ) countComment
     ON countComment.feedID = feed.id
   LEFT
   JOIN ( SELECT l.feedID
               , COUNT(1) AS cnt
            FROM feedLike l
           GROUP BY l.feedID
        ) countLike
     ON countLike.feedID = feed.id

---

不同

作为一种完全不同的方法，您可以让查询生成部分交叉乘积（给定 feedID 的FeedLike中的每一行都与 FeedComment 中的每一行匹配），然后计算"非重复"id值。但这种方法有可能产生相当大（和性能降低）的中间结果。（如果给定feed有 30 行feedLike和 30 行feedComment，则单个 Feed 总共有 1 x 30 x 30 = 900 行。

我故意让这个例子不完整，以阻止这是一般做法。但我包括它，因为它确实展示了另一种方法：

 SELECT feed.id
      , ...
      , COUNT(DISTINCT feedLike.ID)
      , COUNT(DISTINCT feedComment.ID)
   FROM feed
   LEFT
   JOIN feedLike
     ON ...
   LEFT
   JOIN feedComment
     ON ...
  GROUP BY feed.id

Answer 2

为什么不使用子查询？（至少在SQL服务器上工作）

Select `feed`.`id` AS `feedID`,
        `feed`.`companyID`,
        `feed`.`eventID`,
        `feed`.`memberID` AS `personID`,
        `feed`.`title`,
        `feed`.`text`,
        `feed`.`shares`,
        UNIX_TIMESTAMP(`feed`.`date`) AS `date`,
        (Select count(`feedComment`.`id`) from feedComment Where commentid = F.id) as 'Comment',
        (Select count('feedLike'.'id') from feedLike Where likeid = F.id) as 'Something else'
From feed F

如果你只需要计数，我不明白为什么要使用连接