blocks|key|3495072|text|请检查我的代码下面，我已经用适当的解释标记了问题。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3495073|SELECT+p.id,+
+++++++p.actual_price,+
+++++++min_price,+
+++++++max_price+
FROM+++(SELECT+Min(price)+AS+min_price,+
+++++++++++++++Max(price)+AS+max_price,+
+++++++++++++++product_id+
++++++++FROM+++prices_history+
++++++++WHERE++timestamp+>+'2019-01-01'+
++++++++GROUP++BY+product_id)+AS+h1,+
+++++++products+p+,
+++++++join+(SELECT+Max(timestamp),+
++++++++++++++++++++price,+
++++++++++++++++++++product_id+
+++++++++++++FROM+++prices_history+
+++++++++++++WHERE++timestamp+>+'2019-01-01'+
+++++++++++++GROUP++BY+product_id,+
+++++++++++++++++++++++price)+AS+h2+
+++++++++ON+p.id+=+h2.product_id+
++++++++++++AND+price+=+min_price++--+<=+This+join+is+same+level+with+subquery+h1.
+++++++++++++++++++++++++++++++++++--+you+cannot+use+min_price+here
WHERE++p.id+=+h1.product_id+
GROUP++BY+p.id,+
++++++++++min_price,+
++++++++++max_price,+
++++++++++p.actual_price+
ORDER++BY+p.id;+|code-block|syntax|javascript|3495074|为了解决这个问题，我还用适当的内部连接替换了遗留的逗号分隔连接。|3495075|with+h1+as+
(
++++SELECT+Min(price)+AS+min_price,+
+++++++++++Max(price)+AS+max_price,+
+++++++++++product_id+
++++FROM+++prices_history+
++++WHERE++timestamp+>+'2019-01-01'+
++++GROUP++BY+product_id

),+h2+as+
(
++++SELECT+Max(timestamp),+
++++++++++++price,+
++++++++++++product_id+
+++++FROM+++prices_history+
+++++WHERE++timestamp+>+'2019-01-01'+
+++++GROUP++BY+product_id,+
+++++++++++++++price

)
SELECT+p.id,+
+++++++p.actual_price,+
+++++++min_price,+
+++++++max_price+
FROM++h1,+
inner+join+products+p++
++on+p.id+=+h1.product_id+
++++join++h2+
++++++ON+p.id+=+h2.product_id+
+++++++++AND+price+=+min_price+++
GROUP++BY+p.id,+
++++++++++min_price,+
++++++++++max_price,+
++++++++++p.actual_price+
ORDER++BY+p.id;+|3495076|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|N|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|O|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

Please check my code below, I have marked the problem with proper explaination.

<pre><code>SELECT p.id, 
 p.actual_price, 
 min_price, 
 max_price 
FROM (SELECT Min(price) AS min_price, 
 Max(price) AS max_price, 
 product_id 
 FROM prices_history 
 WHERE timestamp &gt; '2019-01-01' 
 GROUP BY product_id) AS h1, 
 products p ,
 join (SELECT Max(timestamp), 
 price, 
 product_id 
 FROM prices_history 
 WHERE timestamp &gt; '2019-01-01' 
 GROUP BY product_id, 
 price) AS h2 
 ON p.id = h2.product_id 
 AND price = min_price -- &lt;= This join is same level with subquery h1.
 -- you cannot use min_price here
WHERE p.id = h1.product_id 
GROUP BY p.id, 
 min_price, 
 max_price, 
 p.actual_price 
ORDER BY p.id; 
</code></pre>

For fixing this, I also replaced legacy comma seperated join with proper inner join

<pre><code>with h1 as 
(
 SELECT Min(price) AS min_price, 
 Max(price) AS max_price, 
 product_id 
 FROM prices_history 
 WHERE timestamp &gt; '2019-01-01' 
 GROUP BY product_id

), h2 as 
(
 SELECT Max(timestamp), 
 price, 
 product_id 
 FROM prices_history 
 WHERE timestamp &gt; '2019-01-01' 
 GROUP BY product_id, 
 price

)
SELECT p.id, 
 p.actual_price, 
 min_price, 
 max_price 
FROM h1, 
inner join products p 
 on p.id = h1.product_id 
 join h2 
 ON p.id = h2.product_id 
 AND price = min_price 
GROUP BY p.id, 
 min_price, 
 max_price, 
 p.actual_price 
ORDER BY p.id; 
</code></pre>

blocks|key|3495130|text|您使用的是“表表达式”。表表达式(如h2)不能引用以前的表表达式(如h1)或其列，但JOIN子句中除外。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|3495131|如果希望h2使用h1，则使用公共表表达式(简称CTE)。您的查询可以改为：|BOLD|3495132|with
h1+as+(
++SELECT+min(price)+as+min_price,+max(price)+as+max_price,+product_id
++FROM+prices_history+
++WHERE+timestamp+>+'2019-01-01'
++GROUP+BY+product_id
),
h2+as+(
++SELECT+max(timestamp),+price,+product_id
++FROM+prices_history+
++WHERE+timestamp+>+'2019-01-01'
++GROUP+BY+product_id,+price
++)
SELECT+p.id,
++p.actual_price,
++h1.min_price,+h1.max_price+
from+products+p
join+h2+ON+p.id+=+h2.product_id
join+h1+on+h2.price+=+h1.min_price
+++++++and+p.id+=+h1.product_id
GROUP+BY+p.id,+h1.min_price,+h1.max_price,+p.actual_price
ORDER+BY+p.id|code-block|syntax|javascript|3495133|entityMap^0|I|2|Y|2|16|4|0|4|2|8|2|E|6|0|0^^$0|@$1|2|3|4|5|6|7|P|8|@$9|Q|A|R|B|C]|$9|S|A|T|B|C]|$9|U|A|V|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|W|8|@$9|X|A|Y|B|C]|$9|Z|A|10|B|C]|$9|11|A|12|B|H]]|D|@]|E|$]]|$1|I|3|J|5|K|7|13|8|@]|D|@]|E|$L|M]]|$1|N|3|-4|5|6|7|14|8|@]|D|@]|E|$]]]|O|$]]

You are using "table expressions". A table expression (such as <code>h2</code>) cannot reference a previous one (such as <code>h1</code>), or its columns, except in the <code>JOIN</code> clause.

If you want to <code>h2</code> to use <code>h1</code>, then use Common Table Expressions (CTEs for short). Your query could be rephrased as:

<pre><code>with
h1 as (
 SELECT min(price) as min_price, max(price) as max_price, product_id
 FROM prices_history 
 WHERE timestamp &gt; '2019-01-01'
 GROUP BY product_id
),
h2 as (
 SELECT max(timestamp), price, product_id
 FROM prices_history 
 WHERE timestamp &gt; '2019-01-01'
 GROUP BY product_id, price
 )
SELECT p.id,
 p.actual_price,
 h1.min_price, h1.max_price 
from products p
join h2 ON p.id = h2.product_id
join h1 on h2.price = h1.min_price
 and p.id = h1.product_id
GROUP BY p.id, h1.min_price, h1.max_price, p.actual_price
ORDER BY p.id
</code></pre>

blocks|key|1963094|text|当然，除了现有的答案之外，您还可以使用派生表使用原始查询，只需将隐式更改为显式联接：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1963095|SELECT+p.id,+p.actual_price,
+++h1.min_price,+h1.max_price+
FROM+(
++SELECT+min(price)+as+min_price,+max(price)+as+max_price,+product_id
++FROM+prices_history+
++WHERE+timestamp+>+'2019-01-01'
++GROUP+BY+product_id
++)+AS+h1
JOIN
++products+p
ON+p.id+=+h1.product_id
JOIN+(
++SELECT+max(timestamp),+price,+product_id
++FROM+prices_history+
++WHERE+timestamp+>+'2019-01-01'
++GROUP+BY+product_id,+price
++)+AS+h2
++ON++h2.product_id+=+p.id+
+++++AND+h2.price+=+h1.min_price
+GROUP+BY+p.id,+p.actual_price,+h1.min_price,+h2.max_price
+ORDER+BY+p.id;+|code-block|syntax|javascript|1963096|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

In addition to the existing answers, of course you can use your original query using Derived Tables, just change the implicit to an explicit join:

<pre><code>SELECT p.id, p.actual_price,
 h1.min_price, h1.max_price 
FROM (
 SELECT min(price) as min_price, max(price) as max_price, product_id
 FROM prices_history 
 WHERE timestamp &gt; '2019-01-01'
 GROUP BY product_id
 ) AS h1
JOIN
 products p
ON p.id = h1.product_id
JOIN (
 SELECT max(timestamp), price, product_id
 FROM prices_history 
 WHERE timestamp &gt; '2019-01-01'
 GROUP BY product_id, price
 ) AS h2
 ON h2.product_id = p.id 
 AND h2.price = h1.min_price
 GROUP BY p.id, p.actual_price, h1.min_price, h2.max_price
 ORDER BY p.id; 
</code></pre>

Why doesn't Postgres allow me using aggregate function in JOIN? 

<pre><code>SELECT p.id, p.actual_price,
 h1.min_price, h1.max_price 
FROM (
 SELECT min(price) as min_price, max(price) as max_price, product_id
 FROM prices_history 
 WHERE timestamp &gt; '2019-01-01'
 GROUP BY product_id
 ) AS h1,
 products p
JOIN (
 SELECT max(timestamp), price, product_id
 FROM prices_history 
 WHERE timestamp &gt; '2019-01-01'
 GROUP BY product_id, price
 ) AS h2
 ON h2.product_id = p.id 
 AND h2.price = h1.min_price
 WHERE 
 p.id = h1.product_id
 GROUP BY p.id, p.actual_price, h1.min_price, h2.max_price
 ORDER BY p.id;
</code></pre>

ERROR: column "min_price" does not exist
LINE 19: AND price = min_price
 ^
HINT: There is a column named "min_price" in table "h1", but it cannot be referenced from this part of the query.

Aggregare function in ON clause in JOIN

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

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

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

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

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

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

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

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

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

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

为什么Postgres不允许我在联接中使用聚合函数？SELECT p.id, p.actual_price,   h1.min_price, h1.max_price FROM (  SELECT min(price) as min_price, max(price) as max_price, product_id ...

问联接中ON子句中的集合函数
EN

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问联接中ON子句中的集合函数EN