blocks|key|1138246|text|如果您只能在purchase_discounts表中拥有多个值，那么在联接之前将多个purchase_discounts行聚合成一个的子查询可以解决这个问题：|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1138247|SELECT+array_to_json(array_agg(p_values))+FROM+
(+
++SELECT+t.item_id,+t.purchase_items_ids,+t.total_sold,+t.discount_amount+FROM+
++++(+
++++++SELECT+purchase_items.item_id+AS+item_id,
+++++++++++++ARRAY_AGG(purchase_items.id)+AS+purchase_items_ids,
+++++++++++++SUM(purchase_items.sold)+as+total_sold,
+++++++++++++X.discount_amount
+++++++++++++FROM+items
++++++++++++++INNER+JOIN+purchase_items+ON+purchase_items.item_id+=+items.id
++++++++++++++INNER+JOIN+purchase+ON+purchase.id+=+purchase_items.purchase_id
++++++++++++++LEFT+JOIN+(SELECT+purchase_id,+sum(purchase_discounts.discount_amount)+AS+discount_amount+FROM+purchase_discounts+GROUP+BY+purchase_id)+X+ON+X.purchase_id+=+purchase.id
+++++++++++++WHERE+
++++++++++++++purchase.id+=+200
+++++++++++++GROUP+by+
++++++++++++++purchase_items.item_id,
++++++++++++++X.discount_amount
++++)+as+t+
++INNER+JOIN+items+i+ON+i.id+=+t.item_id+
)+AS+p_values;|code-block|syntax|javascript|1138248|entityMap^0|6|I|16|I|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@$9|N|A|O|B|C]|$9|P|A|Q|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|R|8|@]|D|@]|E|$I|J]]|$1|K|3|-4|5|6|7|S|8|@]|D|@]|E|$]]]|L|$]]

If you can have multiple values only in the <code>purchase_discounts</code> table then a subquery that aggregate multiple <code>purchase_discounts</code> rows into one before the join can solve the problem:
<pre><code>SELECT array_to_json(array_agg(p_values)) FROM 
( 
 SELECT t.item_id, t.purchase_items_ids, t.total_sold, t.discount_amount FROM 
 ( 
 SELECT purchase_items.item_id AS item_id,
 ARRAY_AGG(purchase_items.id) AS purchase_items_ids,
 SUM(purchase_items.sold) as total_sold,
 X.discount_amount
 FROM items
 INNER JOIN purchase_items ON purchase_items.item_id = items.id
 INNER JOIN purchase ON purchase.id = purchase_items.purchase_id
 LEFT JOIN (SELECT purchase_id, sum(purchase_discounts.discount_amount) AS discount_amount FROM purchase_discounts GROUP BY purchase_id) X ON X.purchase_id = purchase.id
 WHERE 
 purchase.id = 200
 GROUP by 
 purchase_items.item_id,
 X.discount_amount
 ) as t 
 INNER JOIN items i ON i.id = t.item_id 
) AS p_values;
</code></pre>

blocks|key|1138303|text|LEFT+JOIN不会导致您的复制，我理解您为什么需要它，因为可能不会有任何折扣，但是对于所提供的数据，更改为内部连接会产生相同的结果。因为您使用了ARRAY_AGG(purchase_items.id)，所以您得到了重复的条目。此外，在提供了数据之后，表item和purchase是不必要的。您可以使用sum和distinct+on的窗口版本来减少purchase_id的复制，并消除上述表。最后，可以完全去除中间select+...+)+t。结果：(请参阅演示)|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1138304|select+array_to_json(array_agg(p_values))+
++from+(select+distinct+on+(pi.item_id,+pi.id)
++++++++++++++++pi.item_id
++++++++++++++,+pi.id+purchase_items_ids
++++++++++++++,+sum(pi.sold)+over+(partition+by+pi.item_id)+total_sold+++++++++
++++++++++++++,+sum(pd.discount_amount)+over(partition+by++pi.item_id)++discount_amount
+++++++++++from+purchase_items+pi++
+++++++++++left+join+purchase_discounts+pd+
+++++++++++++on+pd.purchase_id+=+pi.purchase_id+
++++++++++order+by+pi.item_id,+pi.id+++++++++++
+++++++)+as+p_values;+|code-block|syntax|javascript|1138305|entityMap^0|0|9|22|S|3K|4|3P|8|4C|B|4V|B|5S|E|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@$9|N|A|O|B|C]|$9|P|A|Q|B|C]|$9|R|A|S|B|C]|$9|T|A|U|B|C]|$9|V|A|W|B|C]|$9|X|A|Y|B|C]|$9|Z|A|10|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|11|8|@]|D|@]|E|$I|J]]|$1|K|3|-4|5|6|7|12|8|@]|D|@]|E|$]]]|L|$]]

The <code>LEFT JOIN</code> is not causing your duplicates, I understand why you need it as there may not be any discounts, but for the data provided changing to an inner join produces the same result. You are getting duplicate entries because you use <code>ARRAY_AGG(purchase_items.id)</code>. Further, with the data presented, the tables <code>item</code> and <code>purchase</code> are not necessary. You can use the window version of sum and <code>distinct on</code> to reduce the duplication of <code>purchase_id</code>, and eliminate the mentioned tables. Finally the middle <code>select ... ) t</code> can be completely removed. Resulting in: (see demo)
<pre><code>select array_to_json(array_agg(p_values)) 
 from (select distinct on (pi.item_id, pi.id)
 pi.item_id
 , pi.id purchase_items_ids
 , sum(pi.sold) over (partition by pi.item_id) total_sold 
 , sum(pd.discount_amount) over(partition by pi.item_id) discount_amount
 from purchase_items pi 
 left join purchase_discounts pd 
 on pd.purchase_id = pi.purchase_id 
 order by pi.item_id, pi.id 
 ) as p_values; 
</code></pre>

blocks|key|1701986|text|使用关联子查询而不是左联接：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1701987|SELECT+array_to_json(array_agg(p_values))+FROM+
(+
++SELECT+t.item_id,+t.purchase_items_ids,+t.total_sold,+t.discount_amount+FROM+
++++(+
++++++SELECT+purchase_items.item_id+AS+item_id,
+++++++++++++ARRAY_AGG(purchase_items.id)+AS+purchase_items_ids,
+++++++++++++SUM(purchase_items.sold)+as+total_sold,
+++++++++++++SUM((SELECT+SUM(pd.discount_amount)+FROM+purchase_discounts+pd
++++++++++++++++++WHERE+pd.purchase_id+=+purchase.id))+as+discount_amount
+++++++FROM+items
+++++++INNER+JOIN+purchase_items+ON+purchase_items.item_id+=+items.id
+++++++INNER+JOIN+purchase+ON+purchase.id+=+purchase_items.purchase_id
+++++++WHERE+purchase.id+=+200
+++++++GROUP+by+purchase_items.item_id
++++)+as+t+
++INNER+JOIN+items+i+ON+i.id+=+t.item_id+
)+AS+p_values;|code-block|syntax|javascript|1701988|db<>fiddle+demo|offset|length|1701989|输出：|1701990|[{"item_id":1,"purchase_items_ids":[1234],"total_sold":1,"discount_amount":12}]|1701991|entityMap|0|LINK|mutability|MUTABLE|url|https://dbfiddle.uk/?rdbms=postgres_13&fiddle=297520e96dea8665cd6fc4a2ec005516^0|0|0|0|F|0|0|0|0^^$0|@$1|2|3|4|5|6|7|W|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|X|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|Y|8|@]|9|@$I|Z|J|10|1|11]]|A|$]]|$1|K|3|L|5|6|7|12|8|@]|9|@]|A|$]]|$1|M|3|N|5|D|7|13|8|@]|9|@]|A|$E|F]]|$1|O|3|-4|5|6|7|14|8|@]|9|@]|A|$]]]|P|$Q|$5|R|S|T|A|$U|V]]]]

Using correlated subquery instead of LEFT JOIN:
<pre><code>SELECT array_to_json(array_agg(p_values)) FROM 
( 
 SELECT t.item_id, t.purchase_items_ids, t.total_sold, t.discount_amount FROM 
 ( 
 SELECT purchase_items.item_id AS item_id,
 ARRAY_AGG(purchase_items.id) AS purchase_items_ids,
 SUM(purchase_items.sold) as total_sold,
 SUM((SELECT SUM(pd.discount_amount) FROM purchase_discounts pd
 WHERE pd.purchase_id = purchase.id)) as discount_amount
 FROM items
 INNER JOIN purchase_items ON purchase_items.item_id = items.id
 INNER JOIN purchase ON purchase.id = purchase_items.purchase_id
 WHERE purchase.id = 200
 GROUP by purchase_items.item_id
 ) as t 
 INNER JOIN items i ON i.id = t.item_id 
) AS p_values;
</code></pre>
<a href="https://dbfiddle.uk/?rdbms=postgres_13&amp;fiddle=297520e96dea8665cd6fc4a2ec005516" rel="nofollow noreferrer">db&lt;&gt;fiddle demo</a>
Output:
<pre><code>[{&quot;item_id&quot;:1,&quot;purchase_items_ids&quot;:[1234],&quot;total_sold&quot;:1,&quot;discount_amount&quot;:12}]
</code></pre>

blocks|key|1702014|text|我认为左联接不会引起，因为使用内部联接查询结果与左联接相同，在折扣purchase_id=200查询中有两个结果，您可以从row_number中使用与partion_by相同的结果如下：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1702015|ROW_NUMBER()+OVER(PARTITION+BY+purchase_items.id+order+by+purchase_items.id)+rn|code-block|syntax|javascript|1702016|然后选择rn=1，您可以更改sum函数的查询，我认为您可以从partion_by中使用。|1702017|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

I think the left join does not cause, because with the Inner Join query result same as the left join, in discount with purchase_id=200 query has 2 results you can use from row_number with the partion_by same as:
<pre><code>ROW_NUMBER() OVER(PARTITION BY purchase_items.id order by purchase_items.id) rn
</code></pre>
then select rn=1.
you change your query for the sum function, I think that you can use from partion_by.

blocks|key|1702057|text|核心问题是您的LEFT+JOIN会乘以行。请参见：|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1702058|两个SQL左联接产生不正确的结果。|unordered-list-item|1702059|聚合折扣到连接之前的一行。或者使用(不相关)子查询表达式：|1702060|SELECT+json_agg(items)
FROM++(
+++SELECT+pi.item_id
++++++++,+array_agg(pi.id)+AS+purchase_items_ids
++++++++,+sum(pi.sold)+AS+total_sold
++++++++,(SELECT+COALESCE(sum(pd.discount_amount),+0)
++++++++++FROM+++purchase_discounts+pd
++++++++++WHERE++pd.purchase_id+=+200)+AS+discount_amount
+++FROM+++purchase_items+pi
+++WHERE++pi.purchase_id+=+200
+++GROUP++BY+1
+++)+AS+items;|code-block|syntax|javascript|1702061|结果：|1702062|[{"item_id":1,"purchase_items_ids":[1234],"total_sold":1,"discount_amount":12}]|1702063|db<>fiddle+https://dbfiddle.uk/?rdbms=postgres_13&fiddle=f262c9bfff1d1597f5646b35fd2f7415|1702064|我增加了一些额外的改进：|1702065|假设引用完整性由FK约束强制执行，我们根本不需要涉及表purchase和items。|1702066|移除子查询级别，不做任何操作。|1702067|使用json_agg()而不是array_to_json(array_agg())。|1702068|将COALESCE()添加到输出0或NULL中，而不提供折扣。|1702069|由于折扣适用于您的模型中的购买，而不是单个项目，因此对每个项目输出discount_amount都是没有意义的。考虑一下这个查询，返回一个条目数组和一个单独的discount_amount|1702070|SELECT+json_build_object(
+++++++++'items'
+++++++,+json_agg(items)
+++++++,+'discount_amount'
+++++++,+(SELECT+COALESCE(sum(pd.discount_amount),+0)
++++++++++FROM+++purchase_discounts+pd
++++++++++WHERE++pd.purchase_id+=+200)
+++++++)
FROM++(
+++SELECT+pi.item_id
++++++++,+array_agg(pi.id)+AS+purchase_items_ids
++++++++,+sum(pi.sold)+AS+total_sold
+++FROM+++purchase_items+pi
+++WHERE++pi.purchase_id+=+200
+++GROUP++BY+1
+++)+AS+items;|1702071|1702072|{"items"+:+[{"item_id":1,"purchase_items_ids":[1234],"total_sold":1}],+"discount_amount"+:+12}|1702073|db<>fiddle+https://dbfiddle.uk/?rdbms=postgres_13&fiddle=f262c9bfff1d1597f5646b35fd2f7415|1702074|使用json_build_object()来组装JSON对象。|1702075|你的例子中有一个单一的项目是没有太大的启示。我增加了一个购买多个项目，没有折扣，我的小提琴。|1702076|entityMap|0|LINK|mutability|MUTABLE|url|https://stackoverflow.com/questions/12464037/two-sql-left-joins-produce-incorrect-result/12464135#12464135|1|https://dbfiddle.uk/?rdbms=postgres_13&fiddle=f262c9bfff1d1597f5646b35fd2f7415|2|https://www.postgresql.org/docs/current/ddl-constraints.html#DDL-CONSTRAINTS-FK|3|https://www.postgresql.org/docs/current/functions-aggregate.html#FUNCTIONS-AGGREGATE-TABLE|4|https://www.postgresql.org/docs/current/functions-conditional.html#FUNCTIONS-COALESCE-NVL-IFNULL|5|6|https://www.postgresql.org/docs/current/functions-json.html#FUNCTIONS-JSON-CREATION-TABLE^0|7|9|0|0|H|0|0|0|0|0|0|B|26|1|0|0|R|8|10|5|8|4|2|0|0|2|A|F|Q|2|A|3|0|1|A|G|1|I|4|1|A|4|0|X|F|27|F|0|0|0|0|B|26|5|0|2|J|2|J|6|0|0^^$0|@$1|2|3|4|5|6|7|21|8|@$9|22|A|23|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|24|8|@]|D|@$9|25|A|26|1|27]]|E|$]]|$1|I|3|J|5|6|7|28|8|@]|D|@]|E|$]]|$1|K|3|L|5|M|7|29|8|@]|D|@]|E|$N|O]]|$1|P|3|Q|5|6|7|2A|8|@]|D|@]|E|$]]|$1|R|3|S|5|M|7|2B|8|@]|D|@]|E|$N|O]]|$1|T|3|U|5|6|7|2C|8|@]|D|@$9|2D|A|2E|1|2F]]|E|$]]|$1|V|3|W|5|6|7|2G|8|@]|D|@]|E|$]]|$1|X|3|Y|5|H|7|2H|8|@$9|2I|A|2J|B|C]|$9|2K|A|2L|B|C]]|D|@$9|2M|A|2N|1|2O]]|E|$]]|$1|Z|3|10|5|H|7|2P|8|@]|D|@]|E|$]]|$1|11|3|12|5|H|7|2Q|8|@$9|2R|A|2S|B|C]|$9|2T|A|2U|B|C]]|D|@$9|2V|A|2W|1|2X]]|E|$]]|$1|13|3|14|5|H|7|2Y|8|@$9|2Z|A|30|B|C]|$9|31|A|32|B|C]|$9|33|A|34|B|C]]|D|@$9|35|A|36|1|37]]|E|$]]|$1|15|3|16|5|6|7|38|8|@$9|39|A|3A|B|C]|$9|3B|A|3C|B|C]]|D|@]|E|$]]|$1|17|3|18|5|M|7|3D|8|@]|D|@]|E|$N|O]]|$1|19|3|Q|5|6|7|3E|8|@]|D|@]|E|$]]|$1|1A|3|1B|5|M|7|3F|8|@]|D|@]|E|$N|O]]|$1|1C|3|1D|5|6|7|3G|8|@]|D|@$9|3H|A|3I|1|3J]]|E|$]]|$1|1E|3|1F|5|6|7|3K|8|@$9|3L|A|3M|B|C]]|D|@$9|3N|A|3O|1|3P]]|E|$]]|$1|1G|3|1H|5|6|7|3Q|8|@]|D|@]|E|$]]|$1|1I|3|-4|5|6|7|3R|8|@]|D|@]|E|$]]]|1J|$1K|$5|1L|1M|1N|E|$1O|1P]]|1Q|$5|1L|1M|1N|E|$1O|1R]]|1S|$5|1L|1M|1N|E|$1O|1T]]|1U|$5|1L|1M|1N|E|$1O|1V]]|1W|$5|1L|1M|1N|E|$1O|1X]]|1Y|$5|1L|1M|1N|E|$1O|1R]]|1Z|$5|1L|1M|1N|E|$1O|20]]]]

The core problem is that your <code>LEFT JOIN</code> multiplies rows. See:
<ul>
<li><a href="https://stackoverflow.com/questions/12464037/two-sql-left-joins-produce-incorrect-result/12464135#12464135">Two SQL LEFT JOINS produce incorrect result</a></li>
</ul>
Aggregate discounts to a single row before the join. Or use a (uncorrelated) subquery expression:
<pre class="lang-sql prettyprint-override"><code>SELECT json_agg(items)
FROM (
 SELECT pi.item_id
 , array_agg(pi.id) AS purchase_items_ids
 , sum(pi.sold) AS total_sold
 ,(SELECT COALESCE(sum(pd.discount_amount), 0)
 FROM purchase_discounts pd
 WHERE pd.purchase_id = 200) AS discount_amount
 FROM purchase_items pi
 WHERE pi.purchase_id = 200
 GROUP BY 1
 ) AS items;
</code></pre>
Result:
<pre><code>[{&quot;item_id&quot;:1,&quot;purchase_items_ids&quot;:[1234],&quot;total_sold&quot;:1,&quot;discount_amount&quot;:12}]
</code></pre>
db&lt;&gt;fiddle <a href="https://dbfiddle.uk/?rdbms=postgres_13&amp;fiddle=f262c9bfff1d1597f5646b35fd2f7415" rel="nofollow noreferrer">here</a>
I added a couple of additional improvements:
<ul>
<li>Assuming referential integrity enforced by <a href="https://www.postgresql.org/docs/current/ddl-constraints.html#DDL-CONSTRAINTS-FK" rel="nofollow noreferrer">FK constraints</a>, we don't need to involve the tables <code>purchase</code> and <code>items</code> at all.
</li>
<li>Removed a subquery level doing nothing.
</li>
<li>Using <a href="https://www.postgresql.org/docs/current/functions-aggregate.html#FUNCTIONS-AGGREGATE-TABLE" rel="nofollow noreferrer"><code>json_agg()</code></a> instead of <code>array_to_json(array_agg())</code>.
</li>
<li>Added <a href="https://www.postgresql.org/docs/current/functions-conditional.html#FUNCTIONS-COALESCE-NVL-IFNULL" rel="nofollow noreferrer"><code>COALESCE()</code></a> to output <code>0</code> instead or <code>NULL</code> for no discounts.
</li>
</ul>
Since discounts apply to the purchase in your model, not to individual items, it doesn't make sense to output <code>discount_amount</code> for every single item. Consider this query instead to return an array of items and a single, separate <code>discount_amount</code>:
<pre class="lang-sql prettyprint-override"><code>SELECT json_build_object(
 'items'
 , json_agg(items)
 , 'discount_amount'
 , (SELECT COALESCE(sum(pd.discount_amount), 0)
 FROM purchase_discounts pd
 WHERE pd.purchase_id = 200)
 )
FROM (
 SELECT pi.item_id
 , array_agg(pi.id) AS purchase_items_ids
 , sum(pi.sold) AS total_sold
 FROM purchase_items pi
 WHERE pi.purchase_id = 200
 GROUP BY 1
 ) AS items;
</code></pre>
Result:
<pre><code>{&quot;items&quot; : [{&quot;item_id&quot;:1,&quot;purchase_items_ids&quot;:[1234],&quot;total_sold&quot;:1}], &quot;discount_amount&quot; : 12}
</code></pre>
db&lt;&gt;fiddle <a href="https://dbfiddle.uk/?rdbms=postgres_13&amp;fiddle=f262c9bfff1d1597f5646b35fd2f7415" rel="nofollow noreferrer">here</a>
Using <a href="https://www.postgresql.org/docs/current/functions-json.html#FUNCTIONS-JSON-CREATION-TABLE" rel="nofollow noreferrer"><code>json_build_object()</code></a> to assemble the JSON object.
Your example with a single item in the purchase isn't too revealing. I added a purchase with multiple items and no discount to my fiddle.

I have 4 tables:
<ul>
<li>Item</li>
<li>Purchase</li>
<li>Purchase Item</li>
<li>Purchase Discount</li>
</ul>
In these tables, the Purchase Discount has two entries, all the others have only one entry. But when I query them, due to the <code>LEFT JOIN</code>, I'm getting duplicate entries.
This query will be running in a large database, and I heard using <code>DISTINCT</code> will reduce the performance. Is there any other way I can remove duplicates without using <code>DISTINCT</code>?
Here is the <a href="http://sqlfiddle.com/#!17/9a704/3" rel="nofollow noreferrer">SQL Fiddle</a>.
The result shows:
<pre><code>[{&quot;item_id&quot;:1,&quot;purchase_items_ids&quot;:[1234,1234],&quot;total_sold&quot;:2}]
</code></pre>
But the result should come as:
<pre><code>[{&quot;item_id&quot;:1,&quot;purchase_items_ids&quot;:[1234],&quot;total_sold&quot;:1}]
</code></pre>

Prevent duplicate rows when using LEFT JOIN in Postgres without DISTINCT

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

我有四张桌子：项目购买采购项目购买折扣在这些表中，购买折扣有两个条目，其他所有条目只有一个条目。但是当我查询它们时，由于LEFT JOIN，我得到了重复的条目。这个查询将在一个大型数据库中运行，我听说使用DISTINCT会降低性能。没有其他方法可以不用DISTINCT移除重复的这是。结果表明：[{"item_id":1...

问在Postgres中使用左联接时防止重复行
EN

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问在Postgres中使用左联接时防止重复行EN

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问在Postgres中使用左联接时防止重复行
EN