MySQL通过总和问题加入表组

2 回答

• 9

  你需要弄平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 |
  

  请注意，我没有't type those schema in my sqlfiddle post manually, I uses sqlfiddle' s Text to DDL

  我爱http://sqlfiddle.com，它的 Text to 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 |
  

  ---

  上述方法可能仍会生成笛卡尔积，在将其分类为类别（A）之前将子类别（B）展平：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 |
  

  回复于 2024-04-24T11:09:32+08:00
• 1

  表D和表E都通过表C链接到其余表 . 因此，您的第一个查询给出了表D中所有行的笛卡尔乘积，表E中的所有行，并且SUM函数聚合了此笛卡尔积 . 可能你也必须按表C分组，而不仅仅是表A.

  回复于 2024-04-24T11:09:32+08:00