MySQL按总和问题加入表组 [英] MySQL joining tables group by sum issue

查看：36
本文介绍了MySQL按总和问题加入表组的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我在加入表格时遇到问题，这里是示例表格:

表 A:(30 行)

<上一页>╔════╦════════════╦═════════════╗║ ID ║ 名称 ║ 描述 ║╠════╬════════════╬═════════════╣║ 1 ║ 类型 ║ Unicode 艺术 ║║ 2 ║ 页眉 ║ 电子表格 ║║ 3 ║ 自动对齐 ║ 关闭 ║╚════╩════════════╩═════════════╝

表 B:(100 行)

<上一页>╔════╦════════════╦═════════════╦════════╕║ ID ║ 名称 ║ 描述 ║ 表 A ║╠════╬════════════╬═════════════╬════════╕║ 1 ║ 类型 ║ Unicode 艺术 ║ 1 ║║ 2 ║ 页眉 ║ 电子表格 ║ 1 ║║ 3 ║ 自动对齐 ║ 关闭 ║ 2 ║╚════╩════════════╩═════════════╩════════╕

表 C:(8000 行)

<上一页>╔════╦════════════╦═════════════╦════════╕║ ID ║ 文章 ║ 文字 ║ TableB ║╠════╬════════════╬═════════════╬════════╕║ 1 ║ 类型 ║ Unicode 艺术 ║ 1 ║║ 2 ║ 页眉 ║ 电子表格 ║ 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 ║ 日期 ║ 浏览量 ║ 访客 ║ 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),总和(D. 印象数)，总和(E.Views)，总和(电子访客)从一个左连接 BON A.ID=B.TableA左连接 CON B.ID=C.TableB左连接 DON C.ID=D.TableC左连接 EON C.ID=E.TableC通过...分组援助

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

<上一页>选择一个名字，说明，SUM(D.clicks),总和(D. 展示次数)从一个左连接 BON A.ID=B.TableA左连接 CON B.ID=C.TableB左连接 DON 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 的信息相关

试试这个:

选择C.文章，C.文本，COALESCE(D.ClicksSum,0) 作为 ClicksSum,COALESCE(D.ImpressionsSum,0) 作为 ImpressionsSum,COALESCE(E.ViewsSum,0) 作为 ViewsSum,COALESCE(E.VisitorSum,0) AS VisitorSum从C左连接(SELECT TableC, SUM(Clicks) AS ClicksSum, SUM(Impressions) AS ImpressionsSum来自 D分组表C) D ON C.ID=D.TableC左连接(SELECT TableC, SUM(Views) AS ViewsSum, SUM(Visitors) AS visitorSum从 E分组表C) E ON C.ID=E.TableC

输出:

<代码>|文章 |正文 |点击总和印象总和 |视讯 |游客总和 |----------------------------------------------------------------------------------|类型 |Unicode 艺术 |23 |8 |23 |8 ||页眉 |电子表格 |15 |10 |32 |21 ||自动对齐 |关闭 |0 |0 |0 |0 |

请注意，我没有在我的 sqlfiddle 帖子中手动键入这些架构，我使用 sqlfiddle 的 Text to DDL

我喜欢 http://sqlfiddle.com，它的 Text to DDL 甚至可以解析出你的数据ASCII艺术ツ

看到你更清晰的目标(从你的评论)，这可能是它:http://www.sqlfiddle.com/#!2/fccf1/13

选择A.名称，A.描述，COALESCE(SUM(D.ClicksSum),0) 作为 ClicksSum,COALESCE(SUM(D.ImpressionsSum),0) 作为 ImpressionsSum,COALESCE(SUM(E.ViewsSum),0) 作为 ViewsSum,COALESCE(SUM(E.VisitorSum),0) AS VisitorSum从C左连接(SELECT TableC, SUM(Clicks) AS ClicksSum, SUM(Impressions) AS ImpressionsSum来自 D分组表C) D ON C.ID=D.TableC左连接(SELECT TableC, SUM(Views) AS ViewsSum, SUM(Visitors) AS visitorSum从 E分组表C) E ON C.ID=E.TableCB.ID = C.TableB 上的右连接 BA.ID = B.TableA 上的右连接按 A.ID 分组

输出:

<代码>|姓名 |说明 |点击总和印象总和 |视讯 |游客总和 |----------------------------------------------------------------------------------|类型 |Unicode 艺术 |38 |18 |55 |29 ||页眉 |电子表格 |0 |0 |0 |0 ||自动对齐 |关闭 |0 |0 |0 |0 |

上述方法可能仍会产生笛卡尔积，在将子类别(B)分组到类别(A)之前将其展平:http://www.sqlfiddle.com/#!2/fccf1/19

选择A.名称，A.描述，COALESCE(SUM(B.ClicksSum),0) 作为 ClicksSum,COALESCE(SUM(B.ImpressionsSum),0) 作为 ImpressionsSum,COALESCE(SUM(B.ViewsSum),0) 作为 ViewsSum,COALESCE(SUM(B.VisitorSum),0) AS VisitorSum从一个左连接(选择B.ID，B.TableA，SUM(C.ClicksSum) 作为 ClicksSum,SUM(C.ImpressionsSum) 作为 ImpressionsSum，SUM(C.ViewsSum) 作为 ViewsSum,SUM(C.VisitorSum) AS 访客总数从 B左连接(选择C.表B，D.ClicksSum,D.ImpressionsSum,E.ViewsSum,E.VisitorSum从C左连接(SELECT TableC, SUM(Clicks) AS ClicksSum, SUM(Impressions) AS ImpressionsSum来自 D分组表C) D ON C.ID=D.TableC左连接(SELECT TableC, SUM(Views) AS ViewsSum, SUM(Visitors) AS visitorSum从 E分组表C) E ON C.ID=E.TableC) C ON C.TableB = B.ID按 B.ID 分组) B ON B.TableA = A.ID按 A.ID 分组

输出:

<代码>|姓名 |说明 |点击总和印象总和 |视讯 |游客总和 |----------------------------------------------------------------------------------|类型 |Unicode 艺术 |38 |18 |55 |29 ||页眉 |电子表格 |0 |0 |0 |0 ||自动对齐 |关闭 |0 |0 |0 |0 |

I have problem with joining tables, here are example tables:

Table A: ( 30 rows)

╔════╦════════════╦═════════════╗
║ ID ║ Name       ║ Description ║
╠════╬════════════╬═════════════╣
║ 1  ║ Type       ║ Unicode Art ║
║ 2  ║ Header     ║ Spreadsheet ║
║ 3  ║ Auto Align ║ Off         ║
╚════╩════════════╩═════════════╝

Table B: ( 100 rows )

╔════╦════════════╦═════════════╦═════════╗
║ ID ║ Name       ║ Description ║ TableA  ║
╠════╬════════════╬═════════════╬═════════╣
║ 1  ║ Type       ║ Unicode Art ║ 1       ║
║ 2  ║ Header     ║ Spreadsheet ║ 1       ║
║ 3  ║ Auto Align ║ Off         ║ 2       ║
╚════╩════════════╩═════════════╩═════════╝

Table C: ( 8000 rows )

╔════╦════════════╦═════════════╦═════════╗
║ ID ║ Article    ║ Text        ║ TableB  ║
╠════╬════════════╬═════════════╬═════════╣
║ 1  ║ Type       ║ Unicode Art ║ 1       ║
║ 2  ║ Header     ║ Spreadsheet ║ 1       ║
║ 3  ║ Auto Align ║ Off         ║ 2       ║
╚════╩════════════╩═════════════╩═════════╝

Table D: ( 100 000 rows and counting )

╔════╦═══════════╦════════════╦═════════════╦═════════╗
║ ID ║ Date      ║ Clicks     ║ Impressions ║ TableC  ║
╠════╬═══════════╬════════════╬═════════════╬═════════╣
║ 1  ║ 20120814  ║ 10         ║ 3           ║ 1       ║
║ 2  ║ 20120815  ║ 13         ║ 5           ║ 1       ║
║ 3  ║ 20120816  ║ 15         ║ 10          ║ 2       ║
╚════╩═══════════╩════════════╩═════════════╩═════════╝

Table E: ( 200 000 rows and counting )

╔════╦═══════════╦════════════╦═══════════╦═════════╗
║ ID ║ Date      ║ Views      ║ Visitors  ║ 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       ║
╚════╩═══════════╩════════════╩═══════════╩═════════╝

I query this tables with single sql statement:

SELECT
       A.name,
       A.Description,
       SUM(D.clicks),
       SUM(D.Impressions),
       SUM(E.Views),
       SUM(E.Visitors)
FROM 
       A 
       LEFT JOIN B
         ON A.ID=B.TableA
       LEFT JOIN C
         ON B.ID=C.TableB
       LEFT JOIN D
         ON C.ID=D.TableC
       LEFT JOIN E
         ON C.ID=E.TableC
GROUP BY
       A.ID

Problem is that the query returns invalid SUM for Table D and Table E
However if query Table D and Table E in invidual queries I get right values:

SELECT
       A.name,
       A.Description,
       SUM(D.clicks),
       SUM(D.Impressions)
FROM 
       A 
       LEFT JOIN B
         ON A.ID=B.TableA
       LEFT JOIN C
         ON B.ID=C.TableB
       LEFT JOIN D
         ON C.ID=D.TableC
GROUP BY
       A.ID

EDIT 1:
I have tried RIGHT JOIN, JOIN, LEFT OUTER JOIN none of them worked,
ofcourse it's possible that I used those in wrong places.
But in the first statement where I got "all included" values are multiplied
many thousand times higher than they really are.

解决方案

You need to flatten both D and E table. Then I suppose A and B are mere lookups for C, so there's no need to do GROUP BY on A: http://www.sqlfiddle.com/#!2/fccf1/8

I removed the noise(A and B), as I can't see(yet) how A and B are related to summarizing the information for C

Try this:

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

Output:

|    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 |

Note that I didn't type those schema in my sqlfiddle post manually, I uses sqlfiddle's Text to DDL

I love http://sqlfiddle.com, its Text to DDL can even parses out the data from your ASCII art ツ

Upon seeing your clearer objective(from your comment), this might be it: 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

Output:

|       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 |

The above approach might still produce cartesian products, flatten the SubCategory(B) before grouping it to 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

Output:

|       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按总和问题加入表组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文
相关文章
数据库最新文章
热门教程
热门工具
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆