mysql 添加总和_MySQL通过总和问题加入表组

你需要弄平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艺术ツ中的数据

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 |