SQL重复的行具有多个左联接 [英] SQL duplicate rows with multiple left joins

问题描述

我目前面临以下问题: 我有3个表，我需要这些表中的信息，而这两个连接都是一对多的.由于某种原因，第二个联接会创建行的重复项，结果第二个返回值被弄乱了(bb.count与第二个联接行的数量相乘)

I'm currently facing the following problem: I have 3 tables I need information from and both of these joins are one to many. For some reason second join creates duplicates of rows and as a result second return value gets messed up (bb.count gets multiplied by the amount of second join rows)

SELECT aa.id, sum(bb.count), count(DISTINCT cc.id)
FROM aaaa aa
LEFT JOIN bbbb bb ON bb.aa_id = aa.id
LEFT JOIN cccc cc ON cc.bb_id = bb.id
GROUP BY aa.id

有没有一种方法可以在不进行其他查询的情况下获得bb.count的正确总和? 删除第二个左联接的那一刻一切都很好，但是不幸的是，我需要它作为第三个返回值，并且如果不导致结果重复(某种)行，就无法对它们进行分组.

Is there a way to get the proper sum of bb.count without another query? The moment I remove second left join everything's fine, unfortunately I need it for the third return value and I can't group them without resulting in a duplicate (sort of) rows in result.

让我们说

bb1.count = 9
bb2.count = 5

有2行，其中cc.bb_id = bb1.id 我得到的结果是23而不是14.

And there's 2 rows where cc.bb_id = bb1.id The result I get is 23 instead of 14.

推荐答案

您在上述查询中遇到了汇总扇出.

You're experiencing aggregate fanout in the above query.

发生这种情况是因为有

• aaa&之间的1-1或1-N连接. bbb
• bbb&之间存在1-N连接. ccc

• either a 1-1 or 1-N join between aaa & bbb
• there is a 1-N join between bbb & ccc

如果通过ccc

要解决该错误，请将查询分为两个CTE，加入结果.

To fix the error, split the query up into two CTEs & join the result.

WITH agg_bb AS (
SELECT aa.id, sum(bb.count)
FROM aaaa aa
LEFT JOIN bbbb bb ON bb.aa_id = aa.id
GROUP BY aa.id
)
, agg_cc AS (SELECT aa.id, count(DISTINCT cc.id)
FROM aaaa aa
LEFT JOIN bbbb bb ON bb.aa_id = aa.id
LEFT JOIN cccc cc ON cc.bb_id = bb.id
GROUP BY aa.id
)
SELECT * FROM agg_bb JOIN agg_cc USING (id)

通常，为避免扇出，请仅对一系列连接中最右边关系中的列应用聚合操作.如果发现您正在汇总中间表中的列，请像上面所做的那样拆分查询.扇出时只有以下函数不变:COUNT DISTINCT，MIN，MAX

In general, to avoid fan outs, only apply aggregate operations to the columns from the rightmost relation in a series of joins. If you find you're aggregating columns from the middle tables, split the query up as I have done above. Only the following functions are invariant across a fan out: COUNT DISTINCT, MIN, MAX

这篇关于SQL重复的行具有多个左联接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！