SQL 查询在每列中返回相同的值

Question

我在查询中遇到 SQL 连接问题，该查询旨在查询已连接到评论、单击和投票表并返回有关每个帖子事件的统计信息的 Post 表。我下面的查询是我一直在使用的。

SELECT
    p.PostID, 
    p.Title, 
    CASE 
        WHEN COUNT(cm.CommentID) IS NULL THEN 0
        ELSE COUNT(cm.CommentID)
    END AS CommentCount, 
    CASE
        WHEN COUNT(cl.ClickID) IS NULL THEN 0 
        ELSE COUNT(cl.ClickID)
    END AS ClickCount, 
    CASE
        WHEN SUM(vt.Value) IS NULL THEN 0
        ELSE SUM(vt.Value)
    END AS VoteScore
FROM 
    Post p
    LEFT OUTER JOIN Comment cm ON p.PostID = cm.PostID
    LEFT OUTER JOIN Click cl ON p.PostID = cl.PostID
    LEFT OUTER JOIN Vote vt ON p.PostID = vt.PostID
GROUP BY
    p.PostID, 
    p.Title

产生以下结果

| PostID | CommentCount | ClickCount | VoteScore |
|--------|--------------|------------|-----------|
| 41     |            60|          60|         60|
| 50     |          1683|        1683|       1683|

这，我知道是不正确的。当注释掉除一个连接之外的所有连接时:

SELECT
     p.PostID 
    ,p.Title 
    ,CASE 
        WHEN COUNT(cm.CommentID) IS NULL THEN 0
        ELSE COUNT(cm.CommentID)
     END AS CommentCount
/*
    ,CASE
        WHEN COUNT(cl.ClickID) IS NULL THEN 0 
        ELSE COUNT(cl.ClickID)
     END AS ClickCount
    ,CASE
        WHEN SUM(vt.Value) IS NULL THEN 0
        ELSE SUM(vt.Value)
     END AS VoteScore
 */
FROM 
    Post p
    LEFT OUTER JOIN Comment cm ON p.PostID = cm.PostID
/*
    LEFT OUTER JOIN Click cl ON p.PostID = cl.PostID
    LEFT OUTER JOIN Vote vt ON p.PostID = vt.PostID
 */
GROUP BY
    p.PostID, 
    p.Title

我得到

| PostID | CommentCount |
|--------|--------------|
| 41     |             3|

哪个是正确的。任何想法我做错了什么？

谢谢。

Answer 1

由于查询生成笛卡尔(或半笛卡尔)乘积，因此预期返回结果。该查询基本上是告诉 MySQL 对从 comment 返回的行执行“交叉连接”操作。 , click和 vote .

从 comment 返回的每一行(对于给定的 postid)匹配来自 click 的每一行(对于相同的 postid)。然后该结果中的每一行都与来自 vote 的每一行相匹配。 (对于相同的 postid)。

因此，对于来自 comment 的两行，以及来自 click 的三行和来自 vote 的四行，这将返回总共 24 (=2x3x4) 行。

解决此问题的常用模式是避免交叉连接操作。

有几种方法可以做到这一点。

选择列表中的相关子查询

如果您只需要三个表中的每一个的单个聚合(例如 COUNT 或 SUM)，您可以删除连接，并在 SELECT 列表中使用相关子查询。例如，编写一个获取单个 postid 计数的查询

 SELECT COUNT(1)
   FROM comment cmt
  WHERE cmt.postid = ?

然后将该查询包装在括号中，并在另一个查询的 SELECT 列表中引用它，并将问号替换为外部查询中引用的表中对 postid 的引用。

SELECT p.postid
     , ( SELECT COUNT(1)
           FROM comment cmt
           WHERE cmt.postid = p.postid
       ) AS comment_count
  FROM post p

重复相同的模式以从 click 获得“计数”和 vote .

这种方法的缺点是 SELECT 列表中的子查询将针对外部查询返回的每一行执行。因此，如果外部查询返回大量行，这可能会变得昂贵。如 comment是一个大表，那么为了获得合理的性能，在 comment 上有合适的可用索引是至关重要的。 .

在线 View 中预聚合

另一种方法是“预聚合”结果内联 View 。编写一个查询，返回 postid 的评论计数。例如

SELECT cmt.postid 
     , COUNT(1) 
  FROM comment cmt
 GROUP BY cmt.postid 

将该查询包装在括号中并在另一个查询的 FROM 子句中引用它，分配一个别名。内联 View 查询基本上取代了外部查询中的表。

SELECT p.postid
     , cm.postid
     , cm.comment_count
  FROM post p 
  LEFT
  JOIN ( SELECT cmt.postid 
              , COUNT(1) AS comment_count
           FROM comment cmt
          GROUP BY cmt.postid
       ) cm
    ON cm.postid = p.postid

并为 click 重复相同的模式和 vote .这里的技巧是内联 View 查询中的 GROUP BY 子句，它保证它不会返回任何重复的 postid 值。并且不会产生重复的笛卡尔积(交叉连接)。

这种方法的缺点是派生表不会被索引。所以对于大量的 postid，在外层查询中执行 join 可能会很昂贵。 (更新的 MySQL 版本通过自动创建适当的索引部分解决了这个缺点。)

(我们可以通过创建一个带有适当索引的临时能力来解决这个限制。但是这种方法需要额外的 SQL 语句，并不完全适合临时单语句。但是对于大集合的批处理，额外的复杂性是值得的以获得一些显着的性能提升。

按 DISTINCT 值折叠笛卡尔积

作为一种完全不同的方法，让您的查询保持原样，使用交叉连接操作，并允许 MySQL 生成笛卡尔积。然后 SELECT 列表中的聚合可以过滤掉重复项。这要求您有来自 comment 的列(或生成的表达式)。对于给定的 postid，评论中的每一行都是唯一的。

SELECT p.postid
     , COUNT(DISTINCT c.id) AS comment_count
  FROM post p
  LEFT
  JOIN comment c
    ON c.postid = p.postid
 GROUP BY p.postid

这种方法的最大缺点是它有可能产生巨大的中间结果，然后通过“使用文件排序”操作(以满足 GROUP BY)“折叠”。这对于大型集来说可能非常昂贵。

这不是实现您希望返回的结果的所有可能查询模式的详尽列表。只是一个有代表性的抽样。