mysql - 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 apart。如果我尝试同时使用它们，那是行不通的。

我该如何实现？

Answer 1

根据目前的情况，您可以添加一个内联 View 以从 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`

parens 之间的查询将被执行，结果将是 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

---

不同

作为一种完全不同的方法，您可以让查询生成部 fork 积(FeedLike 中给定 feedID 的每一行都与 FeedComment 中的每一行相匹配) ，然后计算“不同的”id 值。但是这种方法有可能产生相当大的(并且性能下降)中间结果。 (如果给定的 feed 有 30 行 feedLike 和 30 行在 feedComment 中，则总共有 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