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，我会得到正确的值:
编辑 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 |
相关文章