我有表(bar,baz),它们有一个或多个与另一个表(foo)相关的行。当我将bar & baz连接到foo时,我得到了每个表的每一行的结果。
http://sqlfiddle.com/#!9/1c13f2/1/0
CREATE TABLE foo (`id` int, `value` varchar(5));
INSERT INTO foo (`id`, `value`) VALUES
(1, 'two'),
(2, 'two'),
(3, 'one');
CREATE TABLE bar (`id` int, `foo_id` int, `value` int);
INSERT INTO bar (`id`, `foo_id`, `value`) VALUES
(1, 1, 1),
(2, 1, 1),
(3, 2, 1),
(4, 2, 1),
(5, 3, 1);
CREATE TABLE baz (`id` int, `foo_id` int, `value` int);
INSERT INTO baz (`id`, `foo_id`, `value`) VALUES
(1, 1, 1),
(2, 1, 1),
(3, 2, 1),
(4, 2, 1),
(5, 3, 1);查询:
SELECT foo.value, SUM(bar.value), SUM(baz.value)
FROM foo
JOIN bar ON bar.foo_id = foo.id
JOIN baz ON baz.foo_id = foo.id
GROUP BY foo.id结果:
value SUM(bar.value) SUM(baz.value)
two 4 4
two 4 4
one 1 1预期结果:
value SUM(bar.value) SUM(baz.value)
two 2 2
two 2 2
one 1 1发布于 2019-11-28 00:10:26
结果是来自交叉(半笛卡尔)产品的预期行为,来自bar的多行与来自baz的多行匹配。
为了避免这种情况,我们可以预先聚合来自bar和baz的计数,然后进行连接。
还要考虑,当bar中有匹配的行但baz中没有匹配的行时,预期会出现什么结果。我们是否要从bar返回总数?使用当前的查询,我们不会从bar获得总数。(在示例数据中,考虑从baz中删除行id=5之后查询将返回什么。)
我会这样写这个查询:
SELECT foo.value
, IFNULL( r.tot_bar_value ,0) AS tot_bar_value
, IFNULL( z.tot_baz_value ,0) AS tot_baz_value
FROM foo
LEFT
JOIN ( -- aggregate total from bar
SELECT bar.foo_id
, SUM(bar.value) AS tot_bar_value
FROM bar
GROUP BY bar.foo_id
) r
ON r.foo_id = foo.id
LEFT
JOIN ( -- aggregate total from bar
SELECT baz.foo_id
, SUM(baz.value) AS tot_baz_value
FROM baz
GROUP BY baz.foo_id
) z
ON z.foo_id = foo.id请注意,我们使用外连接来处理bar或baz中不匹配的行时的情况。
为了进行测试,我们可以单独运行括号内的SELECT查询,看看返回了什么。
https://stackoverflow.com/questions/59073476
复制相似问题