MySQL按总和加入表组

我在连接表时遇到问题，以下是示例表:

表A:(30行)

╔════╦════════════╦═════════════╗
║ID║名称║描述║
╠════╬════════════╬═════════════╣
║1║类型║Unicode艺术║
║2║ header ║电子表格║
║3║自动对齐║关║
╚════牛皮════════════牛皮═════════════╝

表B:(100行)

╔════╦════════════╦═════════════╦═════════╗
║ID║名称║描述║TableA║
╠════╬════════════╬═════════════╬═════════╣
║1║类型║Unicode艺术║1║
║2║ header ║电子表格║1║
║3║自动对齐║关║2║
╚════牛皮════════════牛皮═════════════牛皮═════════════

表C :( 8000行)

╔════╦════════════╦═════════════╦═════════╗
║ID║文章║文本║TableB║
╠════╬════════════╬═════════════╬═════════╣
║1║类型║Unicode艺术║1║
║2║ header ║电子表格║1║
║3║自动对齐║关║2║
╚════牛皮════════════牛皮═════════════牛皮═════════════

表D:(100 000行并在计数)

╔════╦═══════════╦════════════╦═════════════╦═════ ════╗
║ID║日期║点击║印象║TableC║
╠════╬═══════════╬════════════╬═════════════╬═════ ════╣
║1║20120814║10║3║1║
║2║20120815║13║5║1║
║3║20120816║15║10║2║
╚════牛皮═══════════牛皮════════════牛皮════════════牛皮═════ ════╝

表E:(200 000行并在计数)

╔════╦═══════════╦════════════╦═══════════╦═══════ ══╗
║ID║日期s View ║访客║TableC║
╠════╬═══════════╬════════════╬═══════════╬═══════ ══╣
║1║20120814║10║3║1║
║2║20120815║13║5║1║
║3║20120816║15║10║2║
║4║20120817║8║7║2║
║5║20120818║9║4║2║
╚════牛皮═══════════牛皮════════════牛皮════════════牛皮═══════ ══╝

我用单个sql语句查询此表:

选择
一个名字，
说明，
SUM(D.clicks)，
SUM(D.Impressions)，
SUM(E.Views)，
总和(E.Visitors)
从
一种
左联接B
开启A.ID = B.TableA
左联接C
在B.ID = C.TableB
左联接D
在C.ID = D.TableC
左联接E
在C.ID = E.TableC
通过...分组
援助

问题是查询返回表D和表E的无效SUM
但是，如果在单个查询中查询表D和表E，我会得到正确的值:

选择
一个名字，
说明，
SUM(D.clicks)，
SUM(D。印象数)
从
一种
左联接B
开启A.ID = B.TableA
左联接C
在B.ID = C.TableB
左联接D
在C.ID = D.TableC
通过...分组
援助

编辑1:
我尝试过RIGHT JOIN，JOIN，LEFT OUTER JOIN，但没有一个起作用，
当然，我可能在错误的地方使用了它们。
但是在我得到“全部包含”的第一个语句中，值相乘
比实际高出数千倍

最佳答案

您需要展平D和E表。然后，我假设A和B只是对C的查找，因此不需要对A进行GROUP BY:http://www.sqlfiddle.com/#!2/fccf1/8

我删除了噪声(A和B)，因为我看不到(至今)A和B与汇总C的信息有何关系

试试这个:

SELECT
       C.Article,
       C.Text,

       COALESCE(D.ClicksSum,0) AS ClicksSum,
       COALESCE(D.ImpressionsSum,0) AS ImpressionsSum,

       COALESCE(E.ViewsSum,0) AS ViewsSum,
       COALESCE(E.VisitorsSum,0) AS VisitorsSum
FROM
       C

       LEFT JOIN
       (
         SELECT TableC, SUM(Clicks) AS ClicksSum, SUM(Impressions) AS ImpressionsSum
         FROM D
         GROUP BY TableC
       ) D ON C.ID=D.TableC

       LEFT JOIN
       (
         SELECT TableC, SUM(Views) AS ViewsSum, SUM(Visitors) AS VisitorsSum
         FROM E
         GROUP BY TableC
       ) E ON C.ID=E.TableC

输出:

|    ARTICLE |        TEXT | CLICKSSUM | IMPRESSIONSSUM | VIEWSSUM | VISITORSSUM |
----------------------------------------------------------------------------------
|       Type | Unicode Art |        23 |              8 |       23 |           8 |
|     Header | Spreadsheet |        15 |             10 |       32 |          21 |
| Auto Align |         Off |         0 |              0 |        0 |           0 |

请注意，我没有在sqlfiddle帖子中手动键入这些架构，而是使用sqlfiddle的文本转换为DDL

我喜欢http://sqlfiddle.com，它的到DDL的文本甚至可以解析ASCII艺术作品中的数据ツ

看到更清晰的目标时(根据您的评论)，可能是:http://www.sqlfiddle.com/#!2/fccf1/13

SELECT
       A.Name, A.Description,

       COALESCE(SUM(D.ClicksSum),0) AS ClicksSum,
       COALESCE(SUM(D.ImpressionsSum),0) AS ImpressionsSum,

       COALESCE(SUM(E.ViewsSum),0) AS ViewsSum,
       COALESCE(SUM(E.VisitorsSum),0) AS VisitorsSum
FROM
       C

       LEFT JOIN
       (
         SELECT TableC, SUM(Clicks) AS ClicksSum, SUM(Impressions) AS ImpressionsSum
         FROM D
         GROUP BY TableC
       ) D ON C.ID=D.TableC

       LEFT JOIN
       (
         SELECT TableC, SUM(Views) AS ViewsSum, SUM(Visitors) AS VisitorsSum
         FROM E
         GROUP BY TableC
       ) E ON C.ID=E.TableC

       RIGHT JOIN B ON B.ID = C.TableB
       RIGHT JOIN A ON A.ID = B.TableA

GROUP BY A.ID

输出:

|       NAME | DESCRIPTION | CLICKSSUM | IMPRESSIONSSUM | VIEWSSUM | VISITORSSUM |
----------------------------------------------------------------------------------
|       Type | Unicode Art |        38 |             18 |       55 |          29 |
|     Header | Spreadsheet |         0 |              0 |        0 |           0 |
| Auto Align |         Off |         0 |              0 |        0 |           0 |

上面的方法可能仍然会产生笛卡尔积，将SubCategory(B)展平，然后再将其分组到Category(A):http://www.sqlfiddle.com/#!2/fccf1/19

SELECT
  A.Name, A.Description,
  COALESCE(SUM(B.ClicksSum),0) AS ClicksSum,
  COALESCE(SUM(B.ImpressionsSum),0) AS ImpressionsSum,
  COALESCE(SUM(B.ViewsSum),0) AS ViewsSum,
  COALESCE(SUM(B.VisitorsSum),0) AS VisitorsSum
FROM A
LEFT JOIN
(

  SELECT
    B.ID, B.TableA,
    SUM(C.ClicksSum) AS ClicksSum,
    SUM(C.ImpressionsSum) AS ImpressionsSum,
    SUM(C.ViewsSum) AS ViewsSum,
    SUM(C.VisitorsSum) AS VisitorsSum
  FROM B
  LEFT JOIN
  (

    SELECT
      C.TableB,

      D.ClicksSum,
      D.ImpressionsSum,

      E.ViewsSum,
      E.VisitorsSum
    FROM
    C

    LEFT JOIN
    (
      SELECT TableC, SUM(Clicks) AS ClicksSum, SUM(Impressions) AS ImpressionsSum
      FROM D
      GROUP BY TableC
    ) D ON C.ID=D.TableC

    LEFT JOIN
    (
      SELECT TableC, SUM(Views) AS ViewsSum, SUM(Visitors) AS VisitorsSum
      FROM E
      GROUP BY TableC
    ) E ON C.ID=E.TableC

  ) C ON C.TableB = B.ID
  GROUP BY B.ID

) B ON B.TableA = A.ID
GROUP BY A.ID

输出:

|       NAME | DESCRIPTION | CLICKSSUM | IMPRESSIONSSUM | VIEWSSUM | VISITORSSUM |
----------------------------------------------------------------------------------
|       Type | Unicode Art |        38 |             18 |       55 |          29 |
|     Header | Spreadsheet |         0 |              0 |        0 |           0 |
| Auto Align |         Off |         0 |              0 |        0 |           0 |

关于MySQL按总和加入表组，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/12026182/